Can I populate other cells based on the selection in a drop down box?

English
, ,
#1

I would like a dropdown type box tied to a specific cell, say cell A1, and when a value is selected from the list in the dropdown, other cells in the same column are populated with data from other sheets in the workbook.

For example, if the dropdown in cell A1 on Sheet1 contains A, B and C, then when A is selected in the dropdown, Cell A2 is filled with data from cell J2 on Sheet2, when B is selected, then cell A2 is filled with data from a cell on Sheet3, and when C is selected, then cell A2 is filled with data from a cell on Sheet4.

I haven’t been able to find this particular usage. Is it possible, and if so, how?

#2

Yes, you can do it.

Create names for the cells whose values should be displayed in A2. For example, give Sheet2.J2 the name AA, Sheet3.J2 the name BB, and the cell Sheet4.J2 the name CC (choose Sheet - Named Ranges and Expressions - Define or press Ctrl + F3)

Note! You can’t give a cell the name C!

Now create a drop-down list for cell A1 with the values A, B and C, and in cell A2 put the formula

=INDIRECT(A1&A1)
#3

Another possibility is to use the feature in Tools > Validity (cell A1 being selected). In the Criteria tab, select List and provide the entries (A, B, C…).

Then in A2, add a condition with IF() for example: if A1=A then show J2 in Sheet2, else, if A1=B then show cell in Sheet3…

The trick is the Validity feature, I guess that you don’t need the details of the IF() syntax.

To show the community your question has been answered, please tag the best answer (), and/or vote for any helpful answer. Else, please edit your question to add information (answers are for solutions only).

#4

LOL, I probably do need to refresh myself on the if syntax. I was trying this method and kept getting an Error 501, whatever that is. I’ll explore the other answer first, and then come back to this as I am more familiar with if than indirect.

#5

Couldn’t understand the other, so since I was more familiar with this, I used it. Works like a charm. Thanks

#6

Thought it might be useful to someone else if I gave the if statement I used to test.

=IF(A1="A","A Shows",IF(A1="B","Now B Shows",IF(A1="C","And Finally C Shows")))
#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.