I have a workbook that has many worksheets pertaining to a project - One for an original contract, one for each change order (for this example), a reference worksheet, and a Contract Summary page.
Each worksheet has up to 75 rows of data. Column A is the FX (element of the project), B is the description, C is the quantity for each element, D is the Material Cost, E and F are two tax amounts, G-M are departments (for which hours are assigned), and N is a sum of C*D, C*E, C*F, etc.
I have each worksheet set up to work properly EXCEPT the Contract Summary.
I have created a SheetList to include all of the worksheets (except the summary and reference/Tax sheet).
I have each FX that exists in either the Original Contract or any Change Order listed once.
The contract Summary should add all of the totals from the SheetList for each FX.
I have tried to accomplish this using a formula but it doesn't work.
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A76"),A2,INDIRECT("'"&SheetList&"'!D2:D76"))) will add all of the values where A2 in the Contract Summary matches A2:76 in the SheetList, but it will not multiply C2:C76 by that number first.
I have also tried =IF("'"&SheetList&"'!A2:A76"=A2), SUMPRODUCT(INDIRECT("'"&SheetList&"'!C2:C76","'"&SheetList&"'!D2:D76")), but that returns a #VALUE error.
I need a macro that will look at A2 of the Contract Summary and compare it to A2:A76 of each page in the SheetList. If there is a match, then I need it to multiply C2:C76 of the SheetList by D2:D76 and enter the sum of those products into D2 of the Contract Summary (to be repeated for columns E-M).
The end result on the Contract Summary page for FX 1 should be this:
Expected result.jpg
Bookmarks