Hi all -
I am attempting to build an interactive user-app to let the user choose a table name and update P&L values based on selection.
Assumptions
Start: Row 2
End: Row 2831 (Some tables less, this is max size table)
Here is my current frmla
=SUMPRODUCT((INDIRECT($B$1&"!"&$A$2:$A$2831)=B$8)*(INDIRECT($B$1&"!"&$C$2:$C$2831)=$A9)*(INDIRECT($B$1&"!"&$D$2:$D$2831)))
Where:
B$8 is comparing the Unit number
$A9 is comparing descriptive label
$D$2:$D$2831 is range of values to be summed if 2 conditions are met
This frmla is currently returning a #REF! error
So then I tried re-writing the Indirect arg's by placing the COl/Row Ref's in cells
=SUMPRODUCT((INDIRECT($B$1&"!"&$B$3&$B$4)=B$9)*(INDIRECT($B$1&"!"&$C$3&$C$4)=$A10)*(INDIRECT($B$1&"!"&$D$3&$D$4)))
This is also returning a #REF! error
I attached a small sample file
I had to trim all data sheets out but 1
I had to trim out all data but 2 units
Should be enough to test with though
Thanks much
-marc