Linking a formula to named ranges in another workbook

[Tetnus]

Hi All,

Any help would be appreciated.

I have a Target and Source workbooks. I am using a formula to access a named range in the target workbook but I get a SPILL error as well as an excel message advising the formula is incorrect and to check my named ranges etc.

Here is the formula I have in the target workbook:
=SUMPRODUCT('c:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]'!SDatePaid>=$F$1)*('C:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]'!SDatePaid<=$G$1)*(MONTH(SDatePaid)=7)*(SAmtPaid).

Hoping someone can see the error!

[MarvinP]

Hi Tetnus and welcome to the forum,

So you have a named range in your other workbook named "sDatePaid" and you have the same named range in your open workbook? What is the error message? We really need two workbooks to help much. Read the yellow top section on this page.

[Tetnus]

Hi MarvinP,
I've attached the samples.

[Tetnus]

Hi, MarvinP,

Here is the formula I have tried (different from above). I'm getting the Spill error.

=SUMPRODUCT('C:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]'!SDatePaid>=$F$1)*('C:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]'!SDatePaid<=$G$1)*(MONTH('C:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]'!SDatePaid=7)*('C:\Users\kent\OneDrive\Documents\Surefact\Invoices\[1 Invoice+Expenses.xlsm]P&L'!SAmtPaid))

In the spreadhseet samples Source.xlsm has been renamed for upload from[1 Invoice+Expenses.xlsm] and Target.xlsm is where the formula is executed from.

[Tetnus]

Hi MarvinP,

Please delete reference. I got around the issue another way with the end result I was looking for.
Thanks anyway.

[MarvinP]

Great news!! I saw that long formula you were using and was just starting to try to understand it. Now - no need.

Glad you got an answer you needed. I find there are many ways to do problems in Excel.