Forum Discussion
Understanding an INDIRECT Formula
JasonTan25 I just wanted to f/u on the 2 previous comments with a warning. In the link provided by mathetes there is the warning: "Note: INDIRECT is a https://exceljet.net/glossary/volatile-function and can cause performance problems in large or complex worksheets. Use with care." and I want to clarify, explain, and EMPHASIZE that warning. Basically Excel tried to be smart. So let's pretend you have a 10,000 row sheet with a calculation in column D based on Columns A,B and C. If you make a change to something in A, B or C it will re-calculate D but if you make a change elsewhere (and that doesn't affect A, B, C) then it will NOT re-calculate D. Makes sense, right. BUT when you use INDIRECT Excel doesn't know what might affect that calculation because it has to evaluate the function to figure that out and therefore it is considered "volatile" and therefore is ALWAYS re-calculated on ANY change. So if column A pulls data based on using the INDIRECT() function then any time you make any changes on the sheet it will recalculate column A which also means anything dependent on A, like col D, must also get re-calculated even if it really doesn't affect those values (i.e. 20,000+ extra calculations) and hence why it says it "can cause performance problems in large or complex worksheets. Use with care."
Thanks, Matt: That's a great explanation of the word "volatile" as it applies to Excel. In more common daily usage, the word tends to imply--well, here's one of several dictionary definitions--"changeable, mercurial, flighty," which taken together tends to imply "can't be relied on." In practice, however, the Excel meaning--which IS good to know and understand--basically means "can slow things down in a big spreadsheet or workbook." It does not mean "can't be trusted." So I really do appreciate your giving this definition.
On the other hand, I use INDIRECT fairly extensively on one of my largest spreadsheets, and notice no deleterious effects; on the contrary, it enables a single summary sheet to pull data from about 40 subordinate sheets based on the tab name and cell references. I can add another subordinate sheet and once the name is applied to the tab, all the relevant data gets pulled into the summary sheet without any further coding or formulas needed. Which is to say, when used appropriately, where it's not slowing things down, INDIRECT proves to be a good tool, worth knowing about.