Hi, I have the formula below:
=SUMPRODUCT(('Plot 5'!$Y$4:$DH$4='G1'!D$7)*('Plot 5'!$F$6:$F$15='G1'!$C10)*'Plot 5'!$Y$6:$DH$15)
This formula works based on the values in the sheet 'Plot 5'. I have sheets named Plot 1, Plot 2, Plot 3, Plot 4, Plot 5. They are all replicas of one another (layout wise), just with different values in cells.
I'd like to be able to select Plot 1-5 in a dropdown, and then the formula will change to reference each sheet (depending on which one is selected).
I have tried using INDIRECT by doing this:
=SUMPRODUCT(INDIRECT(C6&"!Y4:DH4"='G1'!D$7))*(INDIRECT(C6&"!F6:F15"='G1'!$C9))*(INDIRECT(C6&"!Y6:DH15"))
Where C6 is the drop-down cell.
Not sure what to try next! I know I can replicate them into hidden sheets and refence those but I'd like it all in one forula if possible...
Thanks in advance!
