To create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function.
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.
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 update, while references that shouldn't change, won't. Using INDIRECT stops all changes to the reference, even when columns/rows are inserted or deleted.
Note: INDIRECT is a "volatile function" function, and can cause slow performance in large or complicated workbooks.
At the core, this is a standard VLOOKUP formula. The only difference is the use of INDIRECT to return a valid table array. In the example shown, two named ranges have been created: "table1" (B4:C6), and "table2" (B9:C11)*. In G5, INDIRECT picks up...
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...
The "month" tabs of the worksheet contain a table that looks like this: The VLOOKUP formulas on the summary tab lookup and extract data from the month tabs, by creating a dynamic reference to the sheet name for each month. The lookup value is...
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 reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell 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.