Ask Your Question

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

asked 2021-04-13 18:35:30 +0200

Kirk Ward gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2021-04-13 19:25:54 +0200

updated 2021-04-13 19:28:21 +0200

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).

edit flag offensive delete link more


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.

Kirk Ward gravatar imageKirk Ward ( 2021-04-13 19:48:26 +0200 )edit

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

Kirk Ward gravatar imageKirk Ward ( 2021-04-13 20:36:03 +0200 )edit

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")))
Kirk Ward gravatar imageKirk Ward ( 2021-04-13 20:59:02 +0200 )edit

answered 2021-04-13 19:22:14 +0200

JohnSUN gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-13 18:35:30 +0200

Seen: 29 times

Last updated: Apr 13