Struggling with updating links from a closed worksheet.

[Sean15]

Hello:

I am using SUMIFS to get data from Excel worksheet A into worksheet B. The problem is when worksheet A is closed, the links in worksheets B won’t update and Excel displays #VALUE!

I changed security setting for workbooks links (Trust Center, external content) to automatic update for all workbooks links but Excel display’s #VALUE!

I installed MOREFUNC add-in (discussed here): Help!! Need to update links from a closed workbook ! But I can’t find MOREFUNC in Excel’s list of available add-in so I’m assuming it is not compatible. I really don’t want to mess around with MOREFUNC because the add-in is no longer maintained by its developer.

I tried tricks 1 and 2 described here: How to Update Links Without Opening File in Excel (4 Methods) but I’m still getting #VALUE! error. I didn’t bother going past trick No. 2.

Can someone suggest a solution?

 

[Fluff]

You will need to use sumproduct rather than sumifs, as none of the xxIFS functions work with closed workbooks.

 

[Sean15]

Thank you. Replaced sumifs formulas with sumproduct. Updating links are no longer an issue.

 

[Fluff]

You're welcome & thanks for the feedback.