Forum Discussion
How do I use Excel to show a list from a sheet in a second column, dependant on the first column?
Hi - I have a drop down list in column A - lets say 1, 2, 3, 4.
That list relates to one of 4 sheets of data, say sheet 1, sheet 2, etc
If I select 1, I want it to only show a list of data from sheet 1 in Column B, likewise select if I 2, then only show a list of data from sheet 2 in Column B.
Anyone know how I do this please?
4 Replies
In the attached file, these are the 3 choices of formulas for the drop-down list:
1. Static categories and items using non-volatile CHOOSE:
=CHOOSE(A2,
'Sheet 1'!$A$1:$A$4,
'Sheet 2'!$A$1:$A$4,
'Sheet 3'!$A$1:$A$4,
'Sheet 4'!$A$1:$A$4)
2. Dynamic categories and static items using volatile INDIRECT:
=INDIRECT("'Sheet "&A3&"'!$A$1:$A$4")
3. Dynamic categories and items using non-volatile INDEX:
=CountedItems
My choice is the last one. What about you?
- Please attach your sample file with sample data and sample results.
Twifoo here you go
If without cosmetic when
=CHOOSE($C$2,'Sheet 1'!$A$1:$A$4,'Sheet 2'!$A$1:$A$4,'Sheet 3'!$A$1:$A$4,'Sheet 4'!$A$1:$A$4)
in data validation
