A brief explanation of the INDIRECT function

Using INDIRECT to get a value from another workbook

The INDIRECT function takes a cell address and returns the value contained within the cell.

When designing a spreadsheet or set of spreadsheets it pays off to plan ahead and keep them well organized. Conceptually, using INDIRECT combined with Named Ranges can seem like a great way to do that. You can keep one area of functionality in one workbook and share key results with other dependent workbooks by looking up those values with INDIRECT.

Using named ranges avoids hard-coding explicit address references and allows us to refactor or restructure the referenced workbook later.

Why using INDIRECT is terrible for performance

The INDIRECT function is a volatile function. This means that every time anything in your workbook changes or any time you press F9 to calculate, the INDIRECT function will be called. On its own this may not be such a big deal, but because the INDIRECT function is called repeatedly and calculations that take the result of that as an input will also be called repeatedly.

If the result of the INDIRECT call is an input to some complex calculation or slow function then your spreadsheet will crawl. Every time you change anything the entire calculation will be re-done, even when the change you’ve made has nothing to do with that part of the spreadsheet.

Excel maintains a dependency graph which enables it to know what cells need recalculating after any changes have been made. This allows it to do the minimal number of computations when recalculating a worksheet after a change has been made. This is a very efficient way of minimizing the work that needs to be done so that spreadsheets can update quickly. Using INDIRECT ruins this as anything that is dependent (directly or indirectly) will end up be recalculated every time Excel recalculates.

#python #excel #computer-science