Forum Discussion
auto change references to another sheet in the same workbook (identical formats)
I have the following formula which references "PreviousMonthsSales" sheet. =SUMIF(PreviousMonthSales!$AI:$AI,"Big",PreviousMonthSales!$AE:$AE)+SUMIF(PreviousMonthSales!$AI:$AI,"Medium",PreviousMo...
=SUMPRODUCT(((INDIRECT("'" & G1 & "'!" & H1 &":"& I1)="big")+(INDIRECT("'" & G1 & "'!" & H1 &":"& I1)="medium"))*INDIRECT("'" & G1 & "'!" & H2 &":"& I2))Is this what you are looking for? Instead of adding two SUMIF formulas you can apply SUMPRODUCT and dynamically refer to different sheets and different criteria columns and result columns as well. You have to enter the sheetname in cell G1 and the columns (or ranges) in cells H1, I1, H2 and I2.