Buzz
INDIRECT references remain unchanged when new rows or columns are inserted into the spreadsheet or when you delete any existing rows or columns. Utilizing INDIRECT for dynamic reference ranges might pose a slight challenge for beginners, so pay close attention to Mytour's instructions.
Using INDIRECT Function for Dynamic Reference Ranges
General Formula
=INDIRECT(sheet_name&'!A1')
Explanation
To create a formula containing a dynamic reference range, you can employ the INDIRECT function.
In the example shown in the image above, the formula in cell C6 is:
=INDIRECT(B6&'!A1')
Note: This approach allows you to build a formula where the sheet name is dynamic. For instance, you can change the sheet name and retrieve information from different spreadsheets.
How does this formula work?
The INDIRECT function attempts to evaluate text as a reference to a spreadsheet.
In this example, the Sheet name is in column B, so you concatenate the Sheet name into the reference to cell A1 using the Concatenate function:
=INDIRECT(B6&'!A1')
After concatenation, you get:
=INDIRECT('Sheet1!A1')
INDIRECT recognizes this as a valid reference to cell A1 in Sheet1 and returns the value in A1 (1000).
In cell C7, the formula looks like this:
=INDIRECT(B7&'!A1')
=INDIRECT('Sheet2!A1')
=200
And the formulas in column C are similar.
Spaces in Sheet Names
If the Sheet name contains spaces, you'll need to adjust the formula to wrap the Sheet name in single quotes like this:
=INDIRECT('''&sheet_name&''!A1')
Where sheet_name is the cell address, in the example above, it's B6.
By using the INDIRECT function to create dynamic references, you can apply it to various tasks related to working with dynamic ranges. Additionally, gaining an understanding of summary functions in Excel will help you become more proficient with this tool. Some statistical functions in Excel that you may encounter include functions like LOGINV, CHIINV, and more. Hope this article proves useful for accounting and office administration professionals.
