#VALUE error using SUMIF with closed workbooks

Hello,

I am having the strangest problem and hope someone can figure it out for me.

I am using a SUMIF formula to calculate a particular value from a different workbook. To create the formula I open up both workbooks (the one w/ the formula and the one I'm pulling the numbers from) and create the formula with no problem. The correct value appears and everything seems like it's working fine.

However, if I save and close out of both workbooks, and then open up only the workbook with the formula, excel asks me if I would like to update the links to other data sources. If I click update, the cell w/ the SUMIF formula changes to #VALUE (error).

But... if I then open up the workbook that the formula is linked to, the cell changes back to the correct numerical value.

What I need is for the workbook to be able to hold the correct numerical value, after updating the links, without having to keep the other workbook open.

here is my formula:

=SUMIF('[Dstar - gRAMP+gIP AP.xls]Sheet1'!$A$5:$A$560,$A$1,'[Dstar - gRAMP+gIP AP.xls]Sheet1'!$F$5:$F$560)

Any ideas?

Thanks

If you don't need to keep the formulas, you can make the last step of your macro to select the cells where your formulas are (or just select all cells), and do a 'copy - paste special (values)' on them. I do this with most of the files I'm going to have to give other people, just to keep things simple.

And if you do need to keep the formulas there, you can make the first step of your macro to rewrite the formulas where they need to be.