Indirect and Dynamic Range

[Graham Haughs]

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

 

[Biff]

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

 

[Bob Phillips]

Just use =Class_6 in the DVD

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Biff]

Just to clarify.......

As Bob noted, you can just use =Class_6 as the source if that's the only
range name you have. But, I have a feeling that you do have more (and didn't
tell us!) which is why I suggested the Choose formula. If you do have more
names than the Choose formula can be expanded to handle the other names (up
to 29).

Biff

 

[Graham Haughs]

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

 

[Graham Haughs]

Sorry I have confused it enough without complicating it even more. In
the last post i made an error as I meant that B11 was changing with
multiple lists , not E11 as I typed. The data validation list was
pointing to B11, ie =Indirect(B11) and a large number of named ranges
will be created and will appear as options in cell B11.

Graham

 

[Biff]

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]

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]

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

 

[Biff]

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

 

[Graham Haughs]

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

 

[Harlan Grove]

Graham Haughs wrote...

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. ....

So Class_6 holds the result of an OFFSET call, and it happens to be
based on column F in the Data worksheet. Column F is the 6th column of
that worksheet. Is there a correspondence between columns in the
worksheet and the number in the Class_# defined names? If so, you could
make the validation range

=INDEX(Data!$2:$2,B11):INDEX(Data!$A:$DP,COUNTA(INDEX(Data!$A:$DP,0,B11)),B11)

Otherwise, you'd need a table of these defined names and their current
addresses, use the B11 value as a lookup value into that table to pull
the range address, then use that as the argument to INDIRECT. In that
case, your data validation list reference would be something like

=INDIRECT(VLOOKUP(B11,RefTbl,2,0))

The first column of RefTbl would be your Class_# defined names, and the
second column the corresponding addresses, which could be produced by a
multistep process like

1. Enter text formula in topmost cell of 2nd column of RefTbl,

="=SUBSTITUTE(CELL(""Address"",(Data!A1,"&X101&")),""$A$1,"","""",1)"

where X101 would be the topmost cell in the 1st column of RefTbl. Fill
down so there's one of these formulas for each defined name.

2. Copy the column of formulas and paste special as values on top of
itself.

3. With the range of former formulas still selected, run Edit > Replace
and replace = with =. That might seem to be no change, but it has the
effect of taking text constants that look like formulas and entering
them as formulas. The result should be the range addresses of the
defined names.

 

[Biff]

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,Sheet2!$A$1:$DT$1,0)))-1)

As the source for the drop down: =GetRange

Biff

 

[Biff]

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,Sheet2!$A$1:$DP$1,0)))-1)

Biff

 

[Graham Haughs]

Sorry for delay in reply but was around midnight last time I posted.
This works and works well. I really appreciate the time and effort you
put into this. It may be easy stuff to you but there is no way I would
ever have arrived at what you produced which makes the whole thing a lot
simpler and slicker. Many thanks.

Graham

 

[Graham Haughs]

Many thanks for this Harlan. I am just starting to get my head round it
but it is only 6 o'clock in the morning so it is a slow process! I am
grateful for all your efforts.

Graham

 

[Biff]

I was thinking that you might have 120 ranges scattered all over the place
which would have been pretty complicated to figure out. But, since it's a
contiguous range it's fairly straightfoward. The formula can be simplified
further if you were to just use numbers to indicate which column you needed.
Doing this would eliminate the MATCH functions. For example, entering in
B11, just 6 instead of Class_6. Then you don't have to match the column
header and the formula would be:

=OFFSET(Sheet2!$A$2,,Sheet1!$B$11-1,COUNTA(INDEX(Sheet2!$A:$DP,,Sheet1!$B$11))-1)

Quite a bit shorter.

Biff