Solved

trying to get a google spreadsheet dropdown list on change to change values in other cells

Posted on 2013-05-14
8
529 Views
Last Modified: 2013-05-15
I have a dropdown list with 2 values. When I select one of the values I would like to change values in other cells.  For example, The dropdown list has 'Front Nine' and 'Back Nine' as values. When I select one of these values I want to change cells on the same sheet to show appropriate information for back/front nine (hole numbers,pars).  Can this be done?
0
Comment
Question by:dmalovich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39166917
Sure. This can be done. Please post the worksheet on which you have the drop-down, the sheet where the data are displayed (presumable the same one) and the sheet where the data are extracted from. You can reduce the number of rows and replace real names with generic ones.
0
 

Author Comment

by:dmalovich
ID: 39166938
The dropdown is located in sheet 'Week 1' and the data is found in 'Admin' sheet. I downloaded as ms excel sheet and saved in open office.
Laser-Golf-League.ods
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39166955
I noticed that the formula in Week1!M4 should be
=SUM(D4:L4)

Open in new window

but then I quickly lost direction. It would appear that the same formula should be copied down all of column M and that there should be a similar formula in Admin.Column M, and that quite generally, columns D:N in Week1 should be filled from columns D:N in Admin.
What is not clear is which names are to be copied when 'Front Nine' is selected and which other names represent the 'Back Nine' - or does the list of names never change? In short, the relationship between the numbers in columns Admin!D:N and the names in Admin!A:A isn't clear there and can't, therefore, be transposed to sheet Week1.
Please explain.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:dmalovich
ID: 39166974
Thanks for responding. I just wanted range D3:M4 in 'Admin' sheet to be copied to range D3:M4 in 'Week 1' sheet when user selects 'Front Nine'. Also, when user selects 'Back Nine', I want to copy range D9:M10 from 'Admin' sheet to D3:M4 in 'Week 1' sheet. I'm not doing anything with the player names right now.
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39167002
Please post this formula in the cells in your worksheet Week1 which you wish to read values from Admin.
=INDIRECT(ADDRESS(ROW()+$N$1,COLUMN(),1,1,"Admin")&":"&ADDRESS(ROW()+$N$1,COLUMN(),1,1))

Open in new window

As you see, this formula reads from N1. Please paste the following formula in that cell.
[N1] =IF($K$1="Front Nine",0,6)

Open in new window

Format the number format of N1 as Custom: ;;; which will make the value invisible unless you select the cell.
As an alternative, you can replace each reference to $N$1 in the first formula with all of the formula in N1 (without the = sign). That is a matter of taste or expedience. I prefer shorter formulas, that's all.
I don't have Open Office at my disposal. I hope that everything will work in that program the same way as in mine.
0
 

Author Closing Comment

by:dmalovich
ID: 39167679
Perfect, it worked. Thank you so much. If you don't mind, can you explain how this is working?
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39168309
I'm glad it solved your problem.
The ADDRESS function creates a string like D9 from ADDRESS([Row],[Column]) or ADDRESS(4,9). =INDIRECT(ADDRESS) has the same effect as =D9 except that I can substitute calculated values for both row and column.
The ROW() and COLUMN() functions return the row and column numbers of the cell in which they reside. So, ADDRESS(ROW(),COLUMN()) creates a string of the address of the cell in which the formula resides. This is useful in your solution because the cell references in both target and source sheets are the same, with only the sheet reference being different.
Finally, the difference in address between Front and Back Nine is 6 rows. So, 6 should be added to the result of ROW() depending upon the value in your drop-down. This number is created by the formula in N1.
0
 

Author Comment

by:dmalovich
ID: 39168345
Great job. Thanks again. As I finish off this project, I may need some other help. Keep an eye out for my other questions.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question