Linking data from another workbook using AVERAGEIF

[Drew_]

Hi all.

I am using AVERAGEIF formulas to pull data from another workbook, and it works great as long as the source book is open. When it is closed, a #VALUE error returns. I had this same problem with my SUMIF formula but I broke it down and entered it as an array and that returns the correct value when the source book is closed. How do I get my AVERAGEIF formulas to update correctly when the source book is closed? Thank you

 

[FDibbins]

As Jonmo suggested, it is almost always better to fix the errors, rather than make a work-around