Reference a Range Name contained in a cell

[Orson100]

Hi,

Hopefully somebody might be able to point me in the right direction on what I suspect might be an easy query but one to which I have been unable to find an answer over the past hour or so.

Similar to the INDIRECT() function that allows one to read a cell reference that is contained as a value within a cell, is there a similar function available that will allow one to pass a named range that is contained as a value within a cell to a required dialog.

In my case, I am trying to create a user input-dependent dropdown control where the named range (say ClientID) for the drop-down list is held in a cell say $c$30. However, when I set $c$30 as the input range in the control dialog, the drop-down list consists of a single item: ClientID, rather than the contents of the range to which ClientID refers.

Many thanks in advance once again.

Orson.

[teylyn]

so, you have a list of values that you want to use in a data validation list.
You have given that list a range name, e.g. MyList
You have a cell, i.e. C30 that has the text MyList
In the data validation setup you need to use

=INDIRECT(C30)

If this does not help, please upload a workbook with sufficient data to illustrate the problem.

cheers

[DonkeyOte]

It sounds to me as though teylyn has hit the nail on the head however I would make one additional point...

The source range referenced via INDIRECT can not itself be a Dynamic Named Range - it must be "hardwired".
That is to say in your example if ClientID is itself a DNR then the INDIRECT call from the Validation dialog will not work and you will instead need to use an IF/CHOOSE based formula in your validation dialog so as to display the listing appropriately.

[Orson100]

Thanks very much for the responses,

In this instance, it is Donkeyote who has nailed it for me in fact. It is the case that I was referencing DNRs INDIRECT()ly from the control dialog. I will build a conditional statement to work around this as you suggest.

Thanks again, great forum; although I like to try work things out for myself I have always found a solution when I come here.

Cheers
Orson.