Using Indirect Function to Intersect 2 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

 

[Peter T]

Hi Ryan,

I replicate your problem, in the absence of anything better try this
workaround

=OFFSET(Sheet1!A1,ROW(INDIRECT(A10))-1,COLUMN(INDIRECT(A11))-1)

The named ranges are on sheet1, A10 & A11 contain the names of your named
ranges, effectively your "Cells 1 & 2"
The formula is not on Sheet1, A10 & A11 are on same sheet as the formula

Regards,
Peter T

 

[Guest]

Peter - I posted this on the Functions forum and got another workaround.
= sum(indirect(cell 1) indirect(cell 2))

 

[Peter T]

That's a much better workaround. Thanks for telling this ng!

Regards,
Peter T

 

[Peter T]

Actually, seems even the Sum is not necessary. Surprisingly this works for
me -

=indirect(cell 1) indirect(cell 2)

In my test, cell 1 is D4 and cell D5, containing the row and column names
as text respectively

=INDIRECT(D4) INDIRECT(D5)
or maybe
=(INDIRECT(D4) INDIRECT(D5))

Regards,
Peter T