Excel - Using Indirect Function to Intersect 2 Named Ranges

Asked By Hokievanda on 27-Aug-07 11:52 PM
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 replied on 28-Aug-07 04:30 AM
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

worksheet
other
Hokievanda replied on 28-Aug-07 12:42 PM
Peter - I posted this on the Functions forum and got another workaround.
= sum(indirect(cell 1) indirect(cell 2))
Peter T replied on 28-Aug-07 02:30 PM
That's a much better workaround. Thanks for telling this ng!

Regards,
Peter T

named
named
with
column I
Peter T replied on 28-Aug-07 02:51 PM
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

named
named
with
column I