To create a formula with a "locked" absolute reference – a reference that won't be changed during copy or paste, or when rows and columns are inserted or deleted in a worksheet – you can use the INDIRECT function. In the example shown, the formula in E5 is:
This approach can be useful when a worksheet is routinely edited in a way that would break traditional cell references. This reference to cell B5 won't change during copy/paste/cut operations, or when columns or rows are inserted/deleted in a worksheet.
In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet. For example, this formula:
will continue to return a reference to cell A1 even if row 1, or column A, are deleted.
A reference created with INDIRECT can be used just like a regular cell reference in other formulas. In the worksheet shown above, the formulas in E5:E9 use INDIRECT to create a locked reference that won't change:
Note: if you *rename* a sheet name used in INDIRECT, the reference will break. This happens because the reference is entered as text and therefore is not automatically updated like a normal reference.
Different from absolute and relative references
Using INDIRECT is different from standard absolute, relative, and mixed references. The $ syntax is designed to allow "intelligent" copying and pasting of formulas, so that references that need to change will change, while references that shouldn't change, will not change. Using INDIRECT with text references stops all changes to the reference, even when columns/rows are inserted or deleted, or when a sheet is renamed.
Note: INDIRECT is a "volatile function" function, and can cause slow performance in large or complicated workbooks.
In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the...
In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula: = LARGE ( B4:B13 , 2 ) will return the 2nd largest value in the range B4:B13 which, in the example above, is the number 9. However, if you supply...
In this example the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses the sheet name in a way that can be changed by referring to up a value on the worksheet. The key to the solution is the INDIRECT...
Note: In Excel 365 , the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions. The core of this formula is a string that represents rows. For example, to create an...
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.