Referencing Sheet Names in a Dynamic Table

New Contributor

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!

3 Replies
The inner bit of your first INDIRECT function in your first formula resolves to:

='Vendor_Table[[#All],[Vendor Worksheet]]'!C3

which is not a valid reference to a range of cells.

@Jan Karel Pieterse 

 

Is there an easy way to fix it? The INDIRECT is set up the same in both examples, so I'm assuming there is an adjustment made necessary because one is a table column and the other is a static list, but I'm stuck as to what that would be. 

Not familiar with your setup, but what if you remove this bit: &"!C3"