External Link - #REF Error

I have one file where I am using the SUMPRODUCT function and this is referencing another workbook that it is grabbing information relative to 3 different cells and then returning the sum based on meeting all 3 criteria.

I have the files open and everything is working. I close both files and then open the file with the main data that I am summing from and make necessary changes and close the file as this is shared with others. When I open the file with the SUMPRODUCT it ask I want to update link. I do and the cell shows a #REF error.

Is there a way to get this to update? The only way I can get it to change this is open the file it is referencing and it automatically corrects the issue and shows me the correct information.

Are there some settings that I need to make in order for this to work. I am hoping I don't have to open the main file each time I want this information updated.