#VALUE! Error, unless source link is opened?

Hi, I'm an novice-intermediate Excel user and am experiencing an issue that doesn't make too much sense to me.

I have a SUMIF formula, with an OFFSET as the last half of the range in order to do a dynamic update each month. The links are correct, the formula works, but when I exit out of the source file and update I'm getting a #VALUE! error. When I open it back up, the formula is back and working perfectly. Any idea why this could be? It's in a shared folder, but I have many other formulas on the same page that link to this file and they don't have this problem.

Thanks for any help!

Both SUMIF and OFFSET don't work with closed workbooks, unfortunately.

If you can describe what the formula is doing, then possibly there could be replacement functions that do work with closed workbooks.

Originally Posted by NBVC

Both SUMIF and OFFSET don't work with closed workbooks, unfortunately.

If you can describe what the formula is doing, then possibly there could be replacement functions that do work with closed workbooks.

Ah, then that would be the issue. Thanks. Yes, I'd love to know a good replacement solution. Here is the outline of the formula (and actually I realized it was just SUM, not SUMIF, but same idea):

=SUM('LINK'!$T$5:OFFSET('LINK'!$T$5,0,Initiation!C13-1))

Basically, I have a list of numbers (orders) per month horizontally in the source LINK, and through the "initiation" tab in the original worksheet, I can change the accounting period (C13), which adjusts the ending column in the range of the sum (if I'm explaining this correctly). How would I do this with a closed workbook?

Thanks!

I thought you had SUMIF in there? SUM() should work, it is just the OFFSET...

Try:

=SUM(LINK!$T$5:INDEX(LINK!$5:$5,Initiation!C13-1))

Are you adding the path at all? It needs the path of the other workbook.

I guess I was wrong... the INDEX within the SUM doesn't work either... I am not sure I ever tested that one.. INDEX on it's own works and SUM on it's own works....

These indirect references are a problem with closed workbooks.

If you can't just copy the values to your current workbook then sum them up directly there, then you may need VBA intervention.

Try SUMPRODUCT,

=SUMPRODUCT(--(COLUMN(LINK!$T$5:$AE$5)-COLUMN(LINK!$T$5)+1<=Initiation!C13),LINK!$T$5:$AE$5)

Add your file path.