Compound Reference

[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!

 

[Dave Peterson]

=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.

 

[PeterM]

Perfect!

thank you Dave.

=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.