Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Location: Singapore
Posts: 20
Send a message via Skype™ to GijsKijlstra
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Location: Singapore
Posts: 20
Send a message via Skype™ to GijsKijlstra
Default

Quote:
Originally Posted by Claus Busch View Post
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
Thank you Claus, for your fast response.

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumifs statemenet returns - , how to use in if statement? Robbro Excel Discussion (Misc queries) 2 February 17th 10 02:30 PM
sumifs in linked workbook vrunda Excel Worksheet Functions 1 July 4th 09 01:37 PM
linked workbook cell returns 0 is source workbook cell is blank? Syd[_2_] Excel Worksheet Functions 4 April 13th 09 07:49 PM
sumif returns #VALUE! when linked workbook is closed BrianL Excel Worksheet Functions 6 June 5th 08 03:38 PM
Linked cell returns a 0 where there is no text (sometimes) ragtopcaddy via OfficeKB.com Links and Linking in Excel 3 March 28th 08 01:43 PM


All times are GMT +1. The time now is 12:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"