Excel Question: I have formulas in Sheet A linked to Sheet B. I'd like to change the links from Sheet B to Sheet C or to Sheet D by changing the contents of a cell on Sheet A and not having to "Search and Replace" the worksheet names. Any ideas?
You can do this with the indirect function, which uses the contents of a cell to build an address. Assuming your formulas point to the same cell in the other sheets, e.g., cell B1 on Sheet1 refers to cells B1 on Sheet2 and Sheet3, and that the sheet to refer to is in A1 on Sheet1, you could enter this in B1 on Sheet1:
You could then drag copy and paste to other cells and get relative referencing.
It should be noted that Indirect is a volatile function, which means it recalculates constantly, which means that having a bunch in a workbook will slow it way, way down.
You can use the INDIRECT function.
Normally you refer to a cell like so:
The INDIRECT function takes text and turns it into a cell reference:
where C1 contains the name of your worksheet.