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.

PeterM wrote:
>
> 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

Perfect!

thank you Dave.

"Dave Peterson" wrote:

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