Can you provide more details?

Where EXACTLY are these named ranges located? Are they in a contiguous 120
column block?

Are the names Class_1 to Class_120?

Biff

"Biff" wrote in message
...
I need 120 named ranges

I've never had to deal with that many in this context!

Let me see if I can come up with something. No guarantees!

Biff

"Graham Haughs" wrote in message
...
Thanks for your patience Biff, the problem is that I need 120 named
ranges so I will have to re-think strategy as you have confirmed that
indirect is no use in ths situation.

Graham

Biff wrote:
Exactly how many names do you have? Choose will work with up to 29
names.


I really need the indirect function


Forget about Indirect. It won't work! Indirect needs a TEXT
representation of a reference. When you use =INDIRECT(B11) the name in
B11 evaluates to the FORMULA: =OFFSET(.....................). This is
not a TEXT representation of a reference!

Try this:

List the names in a range of cells, say, AA1:AAn

AA1 = Class_1
AA2 = Class_2
AA3 = Class_3
etc

As the source for the drop down:

=CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class _3)

Biff

"Graham Haughs" wrote in message
...

Thanks for feedback but I really need the indirect function. I know I
can use =Class_6 etc but E11 will be changing, as it is a drop down
list. It may be Class_5, Class_6, Class_12 etc so the data validation
has to pick up the named range which this indicates. If it was not a
dynamic range it works fine, I just do =E11 and whatever value appears
in cell E11 the appropriate named range will appear as the drop down
list, but NOT when it is a dynamic range.
Sorry to be a pain but I really need it to do this.

Graham

Biff wrote:

Hi!

Do you enter more than just "Class_6" into B11?

Try this as the source for the drop down:

=CHOOSE(1,Class_6)

Biff

"Graham Haughs" wrote in message
. ..


I have a named dynamic range Class_6 which holds the dynamic range
=OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1 )
I create a data validation list and put the location as =INDIRECT(B11)
In cell B11 is the value Class_6
When I try to enter =indirect(B11) in the data validation I get the
message "The source currently evaluates to an error"
I can get the indirect to work with a named list but not with a
dynamic range list. Am I doing something wrong or will it not do this.
I value any help.

Kind regards,
Graham Haughs
Turriff
Scotland