Data Validation-Different Sources

Hi,

I'm currently using a drop down menu using a named range on another worksheet. I've now inserted some new worksheets with other named ranges. Can I change the source in my drop down menu depending on what is in another cell?

eg

In A1 I have a drop down menu listing the names of my new three worksheets A, B and C.
In A2 I want a drop down menu showing a list of words in column A from either worksheet A, B or C, depending on what is selected in A1. I can name the ranges in worksheet A, B and C is that helps.

What do I type into the Source box in the Data Validation screen?

Thanks for any help.

Matt

Hi Matt,

Name your ranges in sheets A, B & C. In A2 go to data validation and using the List option type in the formula,

=CHOOSE(-64+CODE(A1),sheet A named range,sheet B named range,sheet C named range)

-64+CODE(A1) tells the CHOOSE function which named range to use in ascending order from 1,2, etc... up to 30 options. If A1 = A CODE(A1) returns the value 65 so -64+65=1 which will tell the data validation to use the first named range, B the second and C the third.

For more advanced Dependent Data Validation techniques check out this site.

http://www.contextures.com/xlDataVal13.html


HTH

Steve