Linked file doesn't update

[scalvitto0815]

I have an excel file (MAIN) that is linked to several other files (a,b,c,d,e...) When open MAIN, the data from a, c, d, e,... are present. The data from b shows #VALUE and does not update until I open it. I don't see anything different in the b file, so why is this happening, and how can I fix it?

 

[Anthony47]

Which formula do you use to link file B? Does it use Indirect or similar conditions?

 

[scalvitto0815]

in the main file the formula I use to link the 414 DataMart... file is as follows:
=SUMIF('Z:\Accounting_New\Statutory\KBIC\Journal Entries\2024\07. July\KBIC\[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$B$7:$B$13,D115,'Z:\Accounting_New\Statutory\KBIC\Journal Entries\2024\07. July\KBIC\[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$F$7:$F$13)

I use similar formulas, in the main file to link to other filess

 

[Anthony47]

Can you show a formula that updates even when the linked file isn't open?
Anyway, here they say SUMIF (and few other functions) requires the addressed workbook to be open, or #VALUE! will be returned: Sumifs using external links returning #VALUE unless source file open

They suggests using SUMPRODUCT instead of SUMIF; so try that way

 

[scalvitto0815]

=+'Z:\Accounting_New\Statutory\KBIC\Journal Entries\2024\07. July\KBIC\[413 Datamart to GL Paid and Paid Exp Adj 07.2024.xlsx]SUPPORT'!F9

Could it be the sumif formula is causing it to not update ?

 

[Anthony47]

Could it be the sumif formula is causing it to not update ?

Yes, it's explained in the link I attached earlier

 

[scalvitto0815]

I'll try it. Thanks for your help !!!

 

[Alex Blakenburg]

You will find that the formulas are much easier to work with if you have the file open while you are working with it since it then doesn't need to show the path.
As Anthony pointed out, you will need to use SumProduct although since you have MS365 Sum will work.

=SUMPRODUCT(
('[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$B$7:$B$13=D115)
*'[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$F$7:$F$13)

=SUM(
('[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$B$7:$B$13=D115)
*'[414 Datamart to GL Case & Exp Reserve Adj 07.2024.xlsx]SUPPORT'!$F$7:$F$13)

 

[scalvitto0815]

Thanks !! getting rid of the sumif and just using + cell worked.

 

[Alex Blakenburg]

Not sure how that is going to give you the same result.