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 f...
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?