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...
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.
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
