Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS linked to other workbook, returns #VALUE!
Hi,
Am Sun, 8 May 2016 12:17:04 +0100 schrieb GijsKijlstra: 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. use SUMPRODUCT to refer to a closed workbook. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
|
|||
|
|||
Quote:
Not familiar with the SUMPRODUCT formula I looked it up, they appeared identical to the SUMIFS formula and changed them as follows: = SUMPRODUCT ('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) Regrettably I still have an #VALUE! message. Can you tell me what I’ve done wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumifs statemenet returns - , how to use in if statement? | Excel Discussion (Misc queries) | |||
sumifs in linked workbook | Excel Worksheet Functions | |||
linked workbook cell returns 0 is source workbook cell is blank? | Excel Worksheet Functions | |||
sumif returns #VALUE! when linked workbook is closed | Excel Worksheet Functions | |||
Linked cell returns a 0 where there is no text (sometimes) | Links and Linking in Excel |