Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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:

=INDIRECT("'"&A$1&"'!"&ADDRESS(ROW(),COLUMN()))

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.

share|improve this answer

You can use the INDIRECT function.

Normally you refer to a cell like so:

=Sheet1!A1

The INDIRECT function takes text and turns it into a cell reference:

=INDIRECT(C1&"!A1")

where C1 contains the name of your worksheet.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.