Make Text-Based Cell References Update Automatically When Copying an INDIRECT Formula

Any reference sitting inside quotation marks is just text, so Excel never treats it as a reference and never shifts it when you copy the formula. To make a range like au$18:au$29 slide across to av$18:av$29, aw$18:aw$29 and so on, you must assemble the reference string at calculation time using ADDRESS and COLUMN instead of hard-coding it in quotes.

Why the reference stays frozen

A formula such as the one below looks like it contains a normal range, but everything between the quotes is a plain text literal:

=INDIRECT("'"&$B8&"'!au$18:au$29")

Excel only adjusts relative and absolute markers ($ and column letters) for actual reference tokens in a formula. A string like "au$18:au$29" has no reference token — it is simply characters. When you drag that formula one column to the right, Excel updates real references in the formula, but the text stays exactly as typed. That is why every pasted copy still points at column au.

Building the reference dynamically

The trick is to generate the column letter from the formula's own position. COLUMN(AU1) returns the number of column au; when the formula is copied right, the AU1 argument shifts to AV1, AW1, and so on, exactly like a normal reference. ADDRESS then converts that number back into an A1-style address, and the 4 as the third argument requests a relative form with no dollar signs.

=INDIRECT("'"&$B8&"'!"&
    ADDRESS(18, COLUMN(AU1), 4) & ":" &
    ADDRESS(29, COLUMN(AU1), 4))

Copy this across columns and the generated range follows: column av, then aw, and so on, while rows 18 and 29 stay locked because they are supplied as plain numbers.

A cleaner version without manual apostrophes

Building the sheet name with "'"&$B8&"'!" works, but ADDRESS can accept the sheet name directly as an optional fifth argument, which removes the fiddly quoting entirely:

=INDIRECT(ADDRESS(18, COLUMN(AU1),,,$B8) & ":" &
          ADDRESS(29, COLUMN(AU1)))

Here $B8 supplies the worksheet name, and ADDRESS produces 'Sheet Name'!au18 style output with the quotes handled for you. This matters if the sheet names ever contain spaces or characters that require quoting.

Consider INDEX instead of INDIRECT

INDIRECT is a volatile function: it recalculates every time anything in the workbook changes, which can noticeably slow a file that uses it in many cells. If the sheet layout is stable, INDEX achieves the same result without volatility and without any text parsing:

=INDEX(Sheet_Name!$A:$AAA, 18, COLUMN(AU1)):
INDEX(Sheet_Name!$A:$AAA, 29, COLUMN(AU1))

This returns the range from row 18 to row 29 in the column matching COLUMN(AU1), and the whole expression can be used anywhere a range is expected — for example inside SUM, AVERAGE, or as a lookup range. The trade-off is that INDEX needs the sheet name written as a real reference (it cannot be taken from cell $B8 as text), so it only works when the target sheet is fixed rather than dynamic.

Choosing between the two approaches

• Use ADDRESS + INDIRECT when the worksheet name must come from a cell and may change at any time.
• Use INDEX when the target sheet is known in advance; it recalculates only when its inputs actually change.
• If your workbook is saved in the older .xls format or needs to support pre-2007 Excel, limit the INDEX column span to $A:$IV, since those versions have fewer than 16,384 columns.

Verifying the result

After pasting the formula across, a quick sanity check prevents silent errors:

1. Select one of the copied cells and inspect the formula — the COLUMN(AU1) argument should now read COLUMN(AV1) (or whatever column you copied into).
2. Use Formulas → Evaluate Formula to watch the ADDRESS calls resolve to the expected strings such as av18 and av29.
3. Confirm the target sheet name in $B8 matches exactly, including spaces; a mismatch returns #REF!.

Prevention tips

• Avoid hard-coding any reference inside quotes; if it must shift on copy, it belongs in a function argument, not in a string.
• Anchor helper cells like $B8 deliberately — decide which parts of the formula should move and which should stay put.
• Reserve INDIRECT for genuinely dynamic references, and prefer INDEX elsewhere to keep recalculation fast.

Frequently Asked Questions

Why doesn't Excel update the reference inside the quotes when I copy?

Because quoted text is a string literal, not a reference. Excel only recalculates and adjusts actual reference tokens during a copy; characters inside quotes are copied verbatim.

What does the 4 in ADDRESS(18, COLUMN(AU1), 4) mean?

It sets the address style. Values 1 through 4 control dollar signs: 1 is fully absolute, 4 is fully relative. Use 4 here so the generated string is a clean au18-style address, though absolute markers would also work inside INDIRECT.

How do I keep the rows fixed while the column changes?

The row numbers are supplied as plain arguments to ADDRESS (18 and 29 in the example), so they never move. Only the COLUMN(AU1) argument shifts when the formula is copied sideways.

Why is INDIRECT considered a problem in large workbooks?

INDIRECT is volatile, meaning Excel recalculates it on every change anywhere in the workbook, even when its inputs are unchanged. Heavy use of it multiplies calculation time; INDEX avoids this because it recalculates only when its referenced cells change.

Can INDIRECT read a sheet name stored in another cell?

Yes — that is its main advantage. Concatenate the cell into the reference string, or pass it as the sheet argument of ADDRESS, as shown above. INDEX cannot do this because it requires a real sheet reference written into the formula.

What error do I get if the sheet name is wrong or missing?

INDIRECT returns a #REF! error when the assembled text does not match an existing sheet. Check for trailing spaces, misspellings, and missing quotation marks around sheet names that contain spaces.