Forum Discussion
Understanding an INDIRECT Formula
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? =-SUMPRODUC...
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.