SOLVED

SUMIF witch linekd documents

Copper Contributor

We have two files named:

Excel-A

and

Excel-B

They are linked with formula is in the Excel-B.xls file in the Utilization tab in row: H9

 

Formula:

=-SUMIF('[Excel-A.xls]Income_new'!$M$49:$M$15000;B9;'[Excel-A.xls]Income_new'!$L$49:$L$15000)

 

And everything is fine until we save the files and want to run them again.

After restarting excel informs me that there are links to external files in the file and gives me three options - Update, Do not update, help. No matter what I choose, change all data to #ARG!

 

After longer clicking in Excel, I noticed that if I first run Excel-A.xls with the editing function. then Excel-B.xls it all works fine.

 

And now my question:

 

What is the reason why it works this way? 

Why does it require me to run both files for the formula to work properly?

 

We have dozens of other files with links from more than 1 file and we cannot know straight from the beginning which one has which links to be sure to open the original/main file first.

We gave you this as a simple example, but we wanted to know what is the reason and how we can avoid the formula breaking.

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution
SUMIF does not work when the file containing the data is not open in Excel. There are several ways to work around this issue, the first being opening the linked file. Alternatively, you could have the file with the SUMIF function pull in the data from File A to a separate worksheet. Data tab, Get Data, From File (this creates a Query). Then you can point your SUMIF to the newly inserted tab. You can set the query to refresh when the file opens so the data on the new tab is up to date.

@Jan Karel Pieterse

 

Thank you for help :) 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution
SUMIF does not work when the file containing the data is not open in Excel. There are several ways to work around this issue, the first being opening the linked file. Alternatively, you could have the file with the SUMIF function pull in the data from File A to a separate worksheet. Data tab, Get Data, From File (this creates a Query). Then you can point your SUMIF to the newly inserted tab. You can set the query to refresh when the file opens so the data on the new tab is up to date.

View solution in original post