Forum Discussion
Worksheet names in cells so that when I change the cell, formulas based off it change
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"))
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.
HansVogelaar I was thinking so too but then the only issue is the data always compares month over month so if I replace all Jan references in February, I will lose the previous month formulas
Referring to your screenshot: if you first replace P&L Company 2022 JAN in formulas with P&L Company 2022 FEB, and then replace P&L company 2021 DEC with P&L Company 2022 JAN, you'll be comparing February '22 with January '22 instead of January '22 with December '21.