Dec 07 2022 12:13 AM
Dec 07 2022 12:13 AM
Hello! I'm new to the company and I get a file that contains INDIRECT Formula which I can't understand about the logic. Could somebody assist me to look into it and explain it to me?
=-SUMPRODUCT('Prod Plan'!B$3:B$78*INDIRECT("'Raw Mat'!$"&$A93&"$2:$"&$A93&"$77"))+IF($A$1="PRD",0,F93+SUMIFS(ME2N!$V:$V,ME2N!$T:$T,$B93,ME2N!$U:$U,G$2))
I can't understand what is the outcome of this formula will be.
Dec 07 2022 05:10 AM
INDIRECT("'Raw Mat'!$"&$A93&"$2:$"&$A93&"$77")) returns a range on the Raw Mat sheet in the column whose letter(s) have been entered in $A93 on the sheet with the formula.
For example, if $A93 contains the letter K, INDIRECT("'Raw Mat'!$"&$A93&"$2:$"&$A93&"$77")) is equivalent to INDIRECT("'Raw Mat'!$K$2:$K$77")), and that evaluates to the range 'Raw Mat'!$K$2:$K$77.
But if $A93 contains the letter Z, then INDIRECT("'Raw Mat'!$"&$A93&"$2:$"&$A93&"$77")) evaluates to 'Raw Mat'!$Z$2:$Z$77.
This range is combined with 'Prod Plan'!B$3:B$78 in SUMPRODUCT: the corresponding cells are multiplied, and the resulting products are summed.
Dec 07 2022 05:28 AM
Dec 07 2022 06:39 AM
@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 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."
Dec 07 2022 07:40 AM
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.