Compound Reference

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

I have a spreadsheet that has 3 columns. I need to be able to make what I
call a compound reference. It's really hard to explain but I need to do the
following:

='Sheet A'!X(b1)

where:
The current sheet is Sheet B
Sheet A is the sheet containing the cell needed
X(b1) refers to column X of sheet A and
the (b1) refers to the cell in sheet B that contains the row number to use
in sheet A for row X

Thanks in advance for your help!
 
=indirect("'sheet a'!x" & b1)
or
=index('sheet a'!x:x,b1)

The =index() formula is better--it only recalculates when something changes in
column X of sheet a (or b1 changes).

The =indirect() formula will recalc whenever excel recalculates.
 
Perfect!

thank you Dave.

Dave Peterson said:
=indirect("'sheet a'!x" & b1)
or
=index('sheet a'!x:x,b1)

The =index() formula is better--it only recalculates when something changes in
column X of sheet a (or b1 changes).

The =indirect() formula will recalc whenever excel recalculates.
Click to expand...
 
Back
Top