Using the indirect formula in Names in data validation

[LennardW]

Hi there,

I'm trying to use Names in data validation. That works, but the Names are defined by the indirect formula. 

So there is 1 drop-down window in which several defined Names are presented, depending of the choice in this drop-down window the list of choices in the second drop-down window changes. This works.

But when the Names, of which the first drop-down window consists, are defined by using the indirect formula it stops working.

I want to use the indirect formula, because the amount of choices that is presented to the user is depending on earlier made choices. (the user can lengthen or shorten the list)

How can I use the indirect formula in a Name and use it successfully in data validation?

Using the formula in a Name and using the name in a cell works, so the formula works, but as soon as I implement it in the data validation it stops working.

Hope someone can help out!

Kind regards,

Lennard

[#2]

@LennardW 

I'm not sure if you mean this, see picture.

If not please just ignore it.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#3]

@LennardW ,,,

INDIRECT considers TEXT value and tries to evaluate them as cell references.

=INDIRECT("A100")       for A100.

INDIRECT converts the text "E1:E20" into the range E1:E20 with function.

=SUM(INDIRECT("E1:E20")) 

With Named Range the formula should :  =INDIRECT("Counrty")

Example above , the TEXT values is Country, INDIRECT converts it to cell references by matching existing Named Range.