Nesting a sheet name reference within a cell reference???

I'm referencing a cell in another worksheet in my workbook, but I want to be
able to define which sheet is referenced in the formula by choosing the sheet
name from another cell. Let's say cell A1 in Sheet1 of the workbook has the
value "Sheet 2" and I want cell A2 in Sheet 1 to reference cell B15 in Sheet
2. Instead of my formula being ='Sheet 2'!B15, I would like the sheet name
to be a separate reference. In other words, I want the formula to be:
='[reference Sheet 1 cell A1 to get the tab name]'!B15.

This way I can create a list with a dropdown box, and change the sheet name
to bring up numbers from different, identically formatted, sheets.

Anyone know how to do this?

try
=indirect("'"&A1&"'!B15")

"Broyston" wrote:

> I'm referencing a cell in another worksheet in my workbook, but I want to be
> able to define which sheet is referenced in the formula by choosing the sheet
> name from another cell. Let's say cell A1 in Sheet1 of the workbook has the
> value "Sheet 2" and I want cell A2 in Sheet 1 to reference cell B15 in Sheet
> 2. Instead of my formula being ='Sheet 2'!B15, I would like the sheet name
> to be a separate reference. In other words, I want the formula to be:
> ='[reference Sheet 1 cell A1 to get the tab name]'!B15.
>
> This way I can create a list with a dropdown box, and change the sheet name
> to bring up numbers from different, identically formatted, sheets.
>
> Anyone know how to do this?

Same questions, but get B15 to increment as it is filled down. TIA

Greg

Many thanks!
B

"bj" wrote:

> try
> =indirect("'"&A1&"'!B15")
>
> "Broyston" wrote:
>
> > I'm referencing a cell in another worksheet in my workbook, but I want to be
> > able to define which sheet is referenced in the formula by choosing the sheet
> > name from another cell. Let's say cell A1 in Sheet1 of the workbook has the
> > value "Sheet 2" and I want cell A2 in Sheet 1 to reference cell B15 in Sheet
> > 2. Instead of my formula being ='Sheet 2'!B15, I would like the sheet name
> > to be a separate reference. In other words, I want the formula to be:
> > ='[reference Sheet 1 cell A1 to get the tab name]'!B15.
> >
> > This way I can create a list with a dropdown box, and change the sheet name
> > to bring up numbers from different, identically formatted, sheets.
> >
> > Anyone know how to do this?

One way:

=INDIRECT("'"&$A$1&"'!B"&ROW(A15))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"GregR" <[email protected]> wrote in message
news:[email protected]...
> Same questions, but get B15 to increment as it is filled down. TIA
>
> Greg
>

RagDyer, thank you

Greg

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"GregR" <[email protected]> wrote in message
news:[email protected]...
> RagDyer, thank you
>
> Greg
>