INDIRECT not working as expected for retrieving Named Ranges

[Jody Wally]

OK, I've scoured the Web and Newsgroups for the answer to
my question, so I hope it's not perfectly obvious...

I am simply trying to use a Named Range in an array
formula by retriving the name from a another cell. The
INDIRECT function should do this, but all I can get is
the #REF! error.

For example, if I have a Range named "Sales" and I have
the text, "Sales" in cell A1 (without the quotes), then a
formula in A3 like,"=SUM(INDIRECT(A1))" ought to be the
equivalent of "=SUM(Sales)", but it doesn't work
(#REF!). It makes no difference if I use this technique
in an array formula or not.

The Range is part of my workbook and works perfectly for
all my other array formulas. What am I doing wrong?
Must I resort to VBA?

Thanks in advance,
Jody

 

[Jody]

Harlan,

The actual names of the ranges I use in this example
are "DRP", "DISP", "MOU", etc. - nothing special or
forbidden. They are dynamic ranges. DRP, for example is
defined as, "=OFFSET(Data!$F$2,0,0,COUNTA(Data!$B:$B_-
1,1)" and is used effectively by arrays in every
worksheet, including the one where I am attampting to use
INDIRECT.

An example of one of these formulas is, "{=SUM(IF
(Date>=E$4,IF(Date<=E$5,IF(ClientNum=$A6,DRP,0))))}"
where E4 and E5 contain Week dates and "ClientNum"
and "Date" are other dynamic ranges.

The point of the exercise is that I want to create one
array formula that I can use for multiple rows, where
each row is headed by a cell with the appropriate range
name in it. i.e. "{=SUM(IF(Date>=E$4,IF(Date<=E$5,IF
(ClientNum=$A6,INDIRECT($C8),0))))}" where C8, C9, and
C10 all have different Range names in them.

Thanks,
Jody

 

[Harlan Grove]

The actual names of the ranges I use in this example
are "DRP", "DISP", "MOU", etc. - nothing special or
forbidden. They are dynamic ranges. DRP, for example is
defined as, "=OFFSET(Data!$F$2,0,0,COUNTA(Data!$B:$B_-
1,1)" and is used effectively by arrays in every
worksheet, including the one where I am attampting to use
INDIRECT.

I suspected your defined names weren't referring to constant ranges.
INDIRECT won't work with dynamic named ranges. You're going to have to use
VBA. *OR* you could use defined names for constant ranges like Data!$F$2 and
Data!$B:$B (such as X and Y), then use

OFFSET(INDIRECT(X),0,0,COUNTA(INDIRECT(Y))-1,1)

 

[Jody]

Dagnabit. I was hoping that I had overlooked some simple
fix.

Oh well, thank you for responding so quickly. Your
solution is appreciated, although since I only have about
5 different rows of data per client, I'll probably just
use nested IFs to determine the correct range to
evaluate - i.e "...if($C8="DRP",DRP,if($C8="MOU",MOU..."
etc.

Thanks again,
Jody

 

[Ken Wright]

Another solution may be to use CHOOSE, eg:-

=CHOOSE(MATCH(C8,{"DRP","MOU","XYZ","ABC"},0),DRP,MOU,XYZ,ABC)

as this will give you some flexibility to increase later if the client so wishes, without running
into a limit of 7 nested IFs