Double data validation (dynamic ranges/lists)

[jbj512]

Hi all, brand new to the boards; relatively new to excel and can't seem to get the following to work.

In cell A1, I want to choose an industry from a list that is dynamic. In cell B1, I want to choose a client from a list that corresponds to the industry selected in A1 - this list is dynamic as well. I used offset to name all ranges (industries, and clients that correspond to each industry)

In A1, I have set the validation as follows...
Allow: List
Source: =Industry
(where Industry is a defined name based on an offset function)
This part works.

However, I get an error when putting the following in B1...
Allow: List
Source: =Indirect(A1)
(where obviously the text in A1 corresponds to another dynamically named range using offset)
I receive this message: "The Source currently evaluates to an error" even when there is an entry in A1

Any ideas? (for reference, everything works perfectly when ranges are not dynamic. also, i checked my offset formulas using f9 in edit mode and everything looks fine there)

Thanks!!

 

[Aladin Akyurek]

Hi all, brand new to the boards; relatively new to excel and can't seem to get the following to work.

In cell A1, I want to choose an industry from a list that is dynamic. In cell B1, I want to choose a client from a list that corresponds to the industry selected in A1 - this list is dynamic as well. I used offset to name all ranges (industries, and clients that correspond to each industry)

In A1, I have set the validation as follows...
Allow: List
Source: =Industry
(where Industry is a defined name based on an offset function)
This part works.

However, I get an error when putting the following in B1...
Allow: List
Source: =Indirect(A1)
(where obviously the text in A1 corresponds to another dynamically named range using offset)
I receive this message: "The Source currently evaluates to an error" even when there is an entry in A1

Any ideas? (for reference, everything works perfectly when ranges are not dynamic. also, i checked my offset formulas using f9 in edit mode and everything looks fine there)

Thanks!!

INDIRECT cannot process a dynamic named range. If you really need ranges defined as such, you could do the following:

Create a range say in A2:A4 on a sheet called Admin, housing the names you used in the definitions like:

Commerce
Education
Industry
Tourism

Name A2:A4 as DynRangeList and invoke now in B1:

=CHOOSE(MATCH(A1,DynRangeList,0),Commerce,Education,Industry,Tourism)