I'd like some help resolving a reference error in a formula designed to use Sumif across multiple sheets.
Here's the problem statement:
a) I'm developing Workbook for managing vendors and consultants for a project. Each Vendor/Consultant has a worksheet for his/her account where we manage contracts, adds, costs to complete, etc. And each vendor is assigned a budget category and budget line item.
b) As part of the workbook, I'd like to maintain one sheet as a dynamic table for use as a vendor roster containing important data for each one. It's important that it be dynamic as we add and adjust vendor information regularly.
c) Another sheet is an Anticipated Cost Report, which I would like to automatically updated as we manage vendor info on the various vendor worksheets.
Here's where I am:
On each sheet C3 is the budget line item assigned to the vendor and, in this instance F3, it the vendor's original contract amount. B39, is the SUMIF criteria to match. Vendor_Worksheet references the column on the vendor roster. From the name manager: "=Vendor_Table[[#All],[Vendor Worksheet]]"
=SUMPRODUCT(SUMIF(INDIRECT("'"&Vendor_Worksheet&"'"&"!C3"),B39,INDIRECT("'"&Vendor_Worksheet&"'"&"!F3")))
With the formula constructed that way, it returns a #REF! error.
Alternatively, if I cut and paste the same info into a "non-dynamic" list, which I've named "Vendor_Testsheet" is seems to work fine. From the name manager: "=Patches!$A$2:$A$15" where patches is the name of the sheet on which I'm keeping my temporary fixes.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Vendor_Testsheet&"'"&"!C3"),B39,INDIRECT("'"&Vendor_Testsheet&"'"&"!F3")))
Can anyone tell me what I'm missing or doing incorrectly to make it work with the table? I'd really appreciate the help.
Thanks!
