May 21 2019 06:28 AM - edited May 21 2019 07:04 AM
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?
May 21 2019 06:50 AM
May 21 2019 10:40 AM
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
May 21 2019 10:14 PM
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?