How to Reference a Different Worksheet Dynamically in Excel?
Dynamic Sheet Reference is a Excel function that dynamically references a cell in a worksheet specified by the name in cell a1.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Using the INDIRECT function, you can easily reference different worksheets by changing a cell value, making your formulas more flexible.
The Formula
"Reference a different worksheet dynamically by using a cell that contains the sheet name, so the formula updates when you change the name"
=INDIRECT(A1 & "!B2")This formula dynamically references a cell in a worksheet specified by the name in cell A1.
Step-by-Step Breakdown
- A1 contains the name of the worksheet you want to reference.
- The ampersand (&) concatenates the worksheet name with the cell reference.
- "!B2" specifies the cell in the referenced worksheet.
- INDIRECT evaluates the concatenated string as a reference to return the value.
Edge Cases & Warnings
- If the sheet name in A1 is misspelled, the formula will return a #REF! error.
- If the referenced sheet is deleted, the formula will also return a #REF! error.
- Using INDIRECT with closed workbooks will not work; the referenced workbook must be open.
Examples
"A1 contains 'Sheet2'; B2 in 'Sheet2' is 100"
100"A1 contains 'Sheet3'; B2 in 'Sheet3' is 'Hello'"
'Hello'Frequently Asked Questions
Can I use INDIRECT with closed workbooks?
No, INDIRECT only works with open workbooks.
What happens if the sheet name contains spaces?
You need to enclose the sheet name in single quotes, like this: 'Sheet Name'.
Is INDIRECT a volatile function?
Yes, INDIRECT is volatile and recalculates whenever any change is made in the workbook.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.