Excel INDIRECT Formula

Why to Use

The INDIRECT formula is a powerful formula that lets you build dynamic references. Any part of the range address – the file path, file name, sheet name, column letter, or row number – can be in a cell that is referenced within the INDIRECT formula. You can reference individual cells or use within other formulas such as VLOOKUP or SUMIFS.

The INDIRECT formula is great for referencing sheets that you routinely delete and replace. Because the INDIRECT formula is looking for the sheet, when it doesn’t find it, it shows a #REF error. However, as soon as that sheet is found again, the #REF error disappears, assuming the rest of the formula is correct.

The INDIRECT formula is also great for quickly building a summary that references the same range values in many identically-structured sheets. If you have an income statement for each unit in your organization, all on separate sheets and want to quickly look at one line item or one date, you can quickly do this with the INDIRECT formula and a listing of sheets, such as the listing created with the XLEV8 Table of Contents macro.

How to Use

The INDIRECT formula is a native function within Excel. It requires you to enter the entire reference, similar to how you would enter a direct reference, but within the INDIRECT() function. Each component of the cell reference (file path, file name, sheet, column, and row), can be located in one or more referenced cells. Note that the file name must be wrapped in [brackets] (if applicable) and the file path, file name, and sheet name must all be wrapped in apostrophes and followed by an exclamation point, just like a normal direct reference. Note that the INDIRECT formula is volatile – if it refers to a range in another file and that file is not open, the formula will result in a #REF error. See the video below for an example.

The XLEV8 Excel add-in contains the Remove Indirect macro that will convert the INDIRECT references in the selected cells to direct references. This can be useful if you want to use the INDIRECT formula to quickly build out the formulas in a sheet, but save the formulas using direct references so that they will not be volatile.

The screenshot below shows how to insert an INDIRECT formula reference and compares it to a direct reference.