don't spend too much time on it.

Eh, most of this stuff is fairly easy! This took me about 10 mins to figure
out AND test. I enjoy problem solving!

Ok........

Assume the 120 ranges are on Sheet2, columns A to DT. Row 1 is the column
headers. Sheet1 B11 is where you enter the desired range name.

Create this named formula:

Name: GetRange
Refers to:

=OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$B$11,Sheet2!$A $1:$DT$1,0)-1,COUNTA(INDEX(Sheet2!$A:$DT,,MATCH(Sheet1!$B$11,S heet2!$A$1:$DT$1,0)))-1)

As the source for the drop down: =GetRange

Biff

"Graham Haughs" wrote in message
...
The ranges are in one worksheet in the main workbook. They are in a
contiguous 120 column block but the number of rows with entries is
variable although there are no blank entries, ie one column may have 4
entries starting at the second row, another may have 15 but there are no
blanks in these entries, and all entries are numerical, non decimal. The
names are Class_1 to Class_120, the top row of each column carrying this
name. Thanks for the help Biff but don't spend too much time on it.
Patience is not a virtue, it is a minor state of despair!

Graham

Graham
Biff wrote:
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