Named Ranges between two separate workbooks

**Aquarock** · 08-09-2019, 11:56 AM

I want to link two separate workbooks using Named Ranges (instead of big cumbersome cell ranges)...no I am not looking for a VBA solution.

I have two workbooks "Book1.xlsx" and "Book2.xslx".

Book 1, I created Named Ranges (i.e. Account, Amount, Year) which I defined in Name Manager, and set them to refer to cells in Book 2 as follows:

ACCOUNT ='[Book 2.xlsx]Sheet1'!$A$1:$A$5
AMOUNT ='[Book 2.xlsx]Sheet1'!$B$1:$B$5
YEAR ='[Book 2.xlsx]Sheet1'!$C$1:$C$5

In Book 1, I use a simple SUMIFS like =IFERROR(SUMIFS(AMOUNT,ACCOUNT,154500,YEAR,2020),"")

It only works when both workbooks are open, if I close Book 2 (the one I am referencing) and use the Edit Links to update Values everything zeros out. I do not have any Named Ranges in Book 2 so there shouldn't be any look up confusion.

Any help/suggestions etc. would be greatly appreciated.

**Glenn Kennedy** · 08-09-2019, 12:17 PM

SUMIF won't work. SUMPRODUCT will.

=SUMPRODUCT((Criterion=A2)*Value)

Download both sheets to the same place. Keep Book 1 closed. Open Book 2. Check that the two named ranges relate to the file structure on your PC. Should be fine...