Using Indirect with Named Ranges

[Guest]

I've got one sheet with named rows & columns. For example row4 is a named
range "Area4" and column C is a named range "Beans". On a seperate worksheet
if I type in;
=Area4 Beans
then I get the intersecting number of beans but if i have two cell with
Cell 1 - Area4
Cell 2 - Beans
and type in another cell;
=Indirect(Cell 1 & " " & Cell 2)
all I get is the corresponding value from Area4 that matches the column I
typed the Indirect function into instead of column C ("Beans") on the other
worksheet.

Any Help would be great!

Thanks,
Ryan

 

[Héctor Miguel]

hi, !

try with: =sum(indirect(cell 1) indirect(cell 2))

hth,
hector.

__ original post __

 

[Guest]

Thanks Hector! I'm not clear why that works but it does.

 

[Héctor Miguel]

hi, !

... I'm not clear why that works but it does.

it's the sum of the intersection [if any] of both ranges

regards,
hector.

 

[Harlan Grove]

... I'm not clear why that works but it does.

it's the sum of the intersection [if any] of both ranges

If the intersection would be a single cell, the SUM call isn't needed.

Two indirect calls are needed because the space intersection operator
is an *operator*, so, e.g., A:A 1:1 isn't a range reference but an
operation on two range references that returns a range reference.
INDIRECT can't/doesn't evaluate range expressions, and that's why the
OP's single INDIRECT call failed.