Linking and Dynamically Sum Data between 2 Workbooks with Multiple Criteria

Hello,

I've been trying to link data between tables in Workbook1 and Workbook2 according to two criteria, then subtotaling corresponding values that align with those two criteria in Workbook2, place that subtotal in the corresponding cell in Workbook1, and finally be able to dynamically update if and when changes are made to the table in Workbook2.

I've tried the following methods but have encountered various problems:

1. SUMPRODUCT formula: Placed in Workbook1 =(SUMPRODUCT(--('Workbook2.xlsx'!Worksheet2[Account2]=INT(LEFT(Account1,6))),--('Workbook2.xlsx'!Worksheet2[Subacct2]=INT(LEFT(Subacct1,4))),--('Workbook2.xlsx'!Worksheet2[DataColumntoSum]))

This formula works and provides the correct calculation when both Workbooks are open, however it does not dynamically update if I just open Workbook1. One of the limitations of the SUMPRODUCT function I discovered is that it does not work well with linkages between closed workbooks.

2. Pivot Tables: I added a pivot table in Workbook1 to summarize the data from the table in Workbook2, and then I used the following GETPIVOTDATA formula to extract data from the pivot table to place in the desired table in Workbook1:

IFERROR(GETPIVOTDATA("DataColumntoPull",'PivotTable'!$A$4,"Account2",Account1,"Subacct2",Subacct1),)

The formula works to pull the correct subtotal from the pivot table, and I had hoped the pivot table summary in Workbook1 would fix the updating problem from the SUMPRODUCT function. However, I learned that Pivot tables with data sources from other workbooks can not be refreshed when they are linked to dynamic table ranges (e.g. Table/Range = 'Workbook2.xlsx'!Table2Name). Whenever I Refresh Data, I get the error: "Reference is Not Valid".

I've attached examples of the two workbooks and their various tables of data for some reference. Ultimately I would like to pull data from Workbook2 (even if the workbook is closed) and dynamically calculate subtotals in the yellow cells in Workbook1, according to their Account and Subacct numbers.

Any help would be greatly appreciated!

(This is my first posting of a question so please forgive any errors in forum etiquette or description).

Thanks,
thegivenbeing