dynamic sheet name in a formula
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? |
dynamic sheet name in a formula
Try
=INDIRECT("'"&referencesheet&"'!D111")/INDIRECT("'"&referencesheet&"'!D101") first indirect should evaluate to 'maryssheet'!D111 and the second to 'maryssheet'!D101 "dogsnapper" wrote: 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? |
dynamic sheet name in a formula
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" wrote: Try =INDIRECT("'"&referencesheet&"'!D111")/INDIRECT("'"&referencesheet&"'!D101") first indirect should evaluate to 'maryssheet'!D111 and the second to 'maryssheet'!D101 "dogsnapper" wrote: 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? |
dynamic sheet name in a formula
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" wrote: 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" wrote: Try =INDIRECT("'"&referencesheet&"'!D111")/INDIRECT("'"&referencesheet&"'!D101") first indirect should evaluate to 'maryssheet'!D111 and the second to 'maryssheet'!D101 "dogsnapper" wrote: 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? |
dynamic sheet name in a formula
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!! "Sheeloo" wrote: 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" wrote: 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" wrote: Try =INDIRECT("'"&referencesheet&"'!D111")/INDIRECT("'"&referencesheet&"'!D101") first indirect should evaluate to 'maryssheet'!D111 and the second to 'maryssheet'!D101 "dogsnapper" wrote: 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? |
All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com