dynamic sheet name in a formula

[dogsnapper]

I would like to have a formula like this:

='maryssheet'!D111/'maryssheet'!D101

but the name of the sheet will change each name, let's say, to "harryssheet"
next month or who knows what the next month's sheet's name will be.

BUT the cell references and the overall formula will stay the same.

therefore, i am thinking that if possible i would like to have a cell on a
sheet called "referencesheet" where i can put the name of the sheet to
substitute in the formula each month that would just change the sheetname in
the formula... or something to that effect.

Help?

 

[Sheeloo]

Try
=INDIRECT("'"&referencesheet&"'!D111")/INDIRECT("'"&referencesheet&"'!D101")
first indirect should evaluate to 'maryssheet'!D111 and the second to
'maryssheet'!D101

 

[dogsnapper]

Hi Sheloo,

This is not working, and i think it is because the cell on reference sheet
is O6.

So, in my formula,

='maryssheet'!D111/'maryssheet'!D101

it would need to actually reference the cell on referencesheet to know what
cell to reference on maryssheet, say something along the lines of:


=INDIRECT("'"&referencesheet&"'!O6")&D111/INDIRECT("'"&referencesheet&"'!O6")&D101

however, this is not working. either.

any suggestions?

Thanks.

 

[Sheeloo]

Sorry... I thought that 'referencesheet' was a NAME referring to the cell
containing the name of the sheet you wanted...

Let us say you want to access D111 & D101 on a sheet named 'Sheet1'

Enter
Sheet1
in cell A1 of any sheet
in cell B1 of any sheet enter

=INDIRECT("'"&A1&"'!D111")/INDIRECT("'"&A1&"'!D101")

If you want your formula in any other sheet and refer to A1 in a sheet named
'Reference Sheet' then use
=INDIRECT('Reference Sheet'!A1&"!D111")/INDIRECT('Reference Sheet'!A1&"!D101")

You need the correct address to D1D101 within INDIRECT using strings and
references to cells containing strings and joining them together with &

 

[dogsnapper]

Hi Sheeloo,

Thanks so much for your help!

I reworked it a little and I got it working correctly per your instructions.
The end statement ended up being:

=INDIRECT("'"&referencesheet!O6&"'!D105")/INDIRECT("'"&referencesheet!O6&"'!D101")

and now it works great!

Thanks again!!