Forum Discussion
Worksheet names in cells so that when I change the cell, formulas based off it change
Hey everyone,
I am working on a project where every month I will get new financials. I would like to set up my worksheet to be dynamic so if I change the cell L2 from "P&L Company 2021 DEC" to "P&L Company 2022 Jan" or any other month, formulas that are looking in the sheet "P&L Company 2021 DEC" will now look in the sheet "P&L Company 2022 Jan".
Is this possible?
Thanks,
5 Replies
You can use the INDIRECT function for this.
For example to return the value of cell D3 on the sheet whose name is in L2:
=INDIRECT("'"&L2&"'!D3")
And to sum the values of E2:E5 on the sheet whose name is in L3:
=SUM(INDIRECT("'"&L3&"'!E2:E5"))
- Hey Hans,
Thanks for the response. Is there a way I can reference the entire sheet with this formula? I basically want to do a lot of reporting using index match, and want to use this in various index match formulas without having a different indirect formula each time.Perhaps an easier option is to leave the formulas as they are now, and to use the Replace dialog to change the sheet name in all formulas.
