flexible drop-down bars

[Arjen]

Hello all,

I have this problem. In worksheet A I have two columns: one column
"variable", and one column "option". It could for example look like
this:
Age Old
Age Young
Age Unknown
Smoking Yes
Smoking No
etc.

Now, in worksheet B I have the same two columns "variable" and
"option", but they are empty. I would like to have a dropdown-function
for column "variable", in which you can select one of the variables
from worksheet A (age, smoking). Based on my selection, the options in
column "option" would then be either old-young-etc or yes-no, presented
in a drop-down bar.

Can this be done in Excel? I know how to make dropdown boxes, but I
don't know how to create them so that they work dynamically -
considering that both input in worksheet A and B are flexible.

Thanks for your advice.

Arjen

 

[Arvi Laanemets]

Hi

You have to use dynamic named ranges as data validation lists sources (you
have to use named ranges anyway when you want to refer in data validation
list to another sheet)

Insert > Name > Define
AgeList=OFFSET(A!$A$1,1,,COUNTA(A!$A:$A)-1,1)
HabitList=OFFSET(A!$B$1,1,,COUNTA(A!$B:$B)-1,1)

(I assumed you have column captions in A!A1:B1 - when not, then remove -1
from formulas)

Now define your data validation lists sources as
=AgeList
or
=HabitList