Ooops!

The formula references a range of 124 columns (column DT), not 120 (column
DP)!

Here is the formula with the correct column range:

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

Biff

"Biff" wrote in message
...
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