Excel advanced indirect dynamic reference

How to Create Dynamic Cell References in Excel?

Dynamic Cell Reference is a Excel function that constructs a cell reference based on text in another cell, allowing for dynamic referencing.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Changing a cell value can dynamically alter your references to different sheets or ranges, streamlining your data management.

Generate This Formula Browse Library

The Formula

Prompt

"Build a cell reference from text, so that changing a cell value changes which sheet or range is referenced"

Excel 
=INDIRECT(A1 & "!B1")

This formula constructs a cell reference based on text in another cell, allowing for dynamic referencing.

Step-by-Step Breakdown

  1. Step 1: Identify the cell (e.g., A1) that contains the sheet name or range.
  2. Step 2: Use the ampersand (&) to concatenate the sheet name with the cell reference (e.g., "!B1").
  3. Step 3: Pass the concatenated string to the INDIRECT function.
  4. Step 4: INDIRECT evaluates the string and returns the value from the specified sheet or range.

Edge Cases & Warnings

  • If the referenced sheet name contains spaces, it must be enclosed in single quotes.
  • If the cell reference is invalid, INDIRECT will return a #REF! error.
  • Changing the sheet name in the reference cell to a non-existent sheet will also cause a #REF! error.

Examples

Prompt

"If A1 contains 'Sheet2', the formula =INDIRECT(A1 & "!B1") returns the value in B1 of Sheet2."

Excel 
Value from Sheet2!B1
Prompt

"If A1 contains 'Sales' and B1 contains 'Total', the formula =INDIRECT(A1 & "!B1") returns the value in B1 of Sales."

Excel 
Value from Sales!B1

Frequently Asked Questions

Can INDIRECT reference cells in closed workbooks?

No, INDIRECT only works with open workbooks.

What happens if the referenced sheet is deleted?

You will receive a #REF! error if the sheet is deleted.

Is INDIRECT volatile?

Yes, INDIRECT is a volatile function 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.

Generate Custom Formula See Examples