Combining cell Ref into formulae

Hi There

Sheet 1 has formulae in cell a1 that says "=sheet2!b2"
Thus value in cell b2 in sheet2 is displayed in a1 in sheet1

Problem I need to change sheet2 to sheet 3 sheet 4 etc etc, so I wanted
to make the text "sheet2" within the formulae above relative, ie
instead of saying "sheet2!b2" I wanted to refer Excel to a cell Say Z1
and then combine it with b2. (sortof like ="z1!&b2" - but this doesnt
work)
In Z1 I would then type Sheet2 or whatever I wanted, it would make
duplicating it much easier.

any ideas welcomed (hope its not all too confusing...

Thanks

D







*** Sent via Developersdex http://www.developersdex.com ***

=INDIRECT(Z1&"!B2")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Darin Kramer" <darin_kramer@hotmail.com> wrote in message
news:O5Xy5bm%23FHA.516@TK2MSFTNGP15.phx.gbl...
>
> Hi There
>
> Sheet 1 has formulae in cell a1 that says "=sheet2!b2"
> Thus value in cell b2 in sheet2 is displayed in a1 in sheet1
>
> Problem I need to change sheet2 to sheet 3 sheet 4 etc etc, so I wanted
> to make the text "sheet2" within the formulae above relative, ie
> instead of saying "sheet2!b2" I wanted to refer Excel to a cell Say Z1
> and then combine it with b2. (sortof like ="z1!&b2" - but this doesnt
> work)
> In Z1 I would then type Sheet2 or whatever I wanted, it would make
> duplicating it much easier.
>
> any ideas welcomed https://www.excelforum.com/images/misc/progress.gif" alt="" /> Register To Reply