SUMIFS linked to other workbook, returns #VALUE!
Besides 2016 data in my 2016 workbook, I have there 2015 data for comparison purposes, with SUMIFS formulas linked to my 2015 workbook. The 2016 workbook presents the correct 2016 values but return an #VALUE! message for the 2015 values, when I open the 2016 workbook and the 2015 workbook is closed. (Clicking “ignore links” or “Update” doesn’t solve the issue). Only when I click “Edit Link” and “Open Source” – effectively opening the 2015 workbook - I get the correct (2015) data. )
I have 720 cells linked to the 2015 workbook in my 2016 workbook, in (the 2016) cell F3 I have the following formula:
=SUMIFS
('Macintosh HD:Desktop:[2015.xlsx]Input'!$F:$F,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$B:$B,$D3,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$G:$G,
F$1)
In the same 2016 workbook in cell H3 I have:
=SUMIFS
('Macintosh HD: Desktop:[2015.xlsx]Input'!$F:$F,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$B:$B,$D3,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$G:$G,
H$1)
Can anyone help me to either fix the formula or recommend an alternative formula to avoid the #VALUE! message or to have the linked workbook also opened?
Thank you in advance, Gijs
|