range name using offset formula vs data validation

[rrporto5]

hello all, my question is a little tricky ... let's go:

What I really want is to create a data validation (list) using a Dependent Drop Down Lists; which means that depending on which text is on cell A1, the drop down list would show a different set of options. For that on the data validation source I am using “indirect” in order to be able to the text on cell A1 be recognized as a range name.

So far so good …

THE PROBLEM IS when an area’s name is not a fixed reference (instead, I am creating a dynamic area using offset formula). The indirect simple does not work! If a literally type the area name into data validation source it works, but using indirect it doesn’t.
Here is the formula I am using =OFFSET('Org Exec'!$B$3;;;COUNTA('Org Exec'!$B:$B)-1).
Also I noticed that when you define a name using the above mentioned formula it desapears form the dropdown list just beside the formula bar.
thx,
Rafael

 

[Jonmo1]

Unfortunately, that's just the way it is.
Indirect can't use a 'dynamic' named range.

Here's an alternate method..

Say your possible Named Range Strings in A1 are
Name1 Name2 and Name3 (whatever)

=CHOOSE(MATCH(A1,{"Name1","Name2","Name3"},0),Name1,Name2,Name3)

 

[rrporto5]

Thx Jonmo1
I am using a mix o CHOOSE in one cell; INDIRECT in another cell and another INDIRECT inside data validation.
than it works!!!!

thx a lot!
Rafael

 

[Jonmo1]

You're welcome.