#VALUE! help needed

[rcmii]

I recently created a file (file X for this example) that has data and graphs. File X pulls data from File Y. Prior to today, I have been able to open File X, the data appears from the previous save and the graphs work. I can see the data in File X without opening File Y to refresh the data.

When I open File X today, I see #VALUE! error messages, despite the fact that when I used this file last week I did not receive this message. The only way I can get the message to go away is to open the linked file, File Y.

A) Why is this ocurring?
B) How do I make this go away, so that I don't have to open File Y to get File X to work (just like it worked up until today)?

 

[morleyuk]

What formulas are you using in file X?

 

[rcmii]

IF(SUMIF(...

I can paste a sample, it it helps. However, all the IF(SUMIF is trying to accomplish is if the sum of the sales for a specific week are 0, then "", otherwise SUMIF each Week by week number.

What's not clear is why I could see data last week, without File Y being open, but now this week I can't see data (other than the error message) with File Y being closed just like last week.

 

[morleyuk]

It's the sumif that is the problem, it will only work with an open workbook.

Previously when you used the saved version you must have opted to 'Don't Update'.

As long as you have sumif's in file X then file Y will need to be open for the formulas to work.

 

[rcmii]

Thanks...I really appreciate the feedback! I never realized that. You bring up a good point because I believe when I originally created the file, I didn't use SUMIF statements but migrated to that as the file was continuing to progress (as it was a BETA file I was putting together). So it's all making more sense. Just when I think I know a lot about Excel, something simple trips me up!

I'll make the changes and see what happens...hope to not bug you again on this.

Richard

 

[rcmii]

One last question....

My co-worker uses the same file and see the #VALUE! message as well. She opens File Y, refreshes, then saves File X. She then sends File X to some people who need to see the data. They are able to open the Sent version of File X and they can see the data. Why would it work that way but not on our side?

 

[morleyuk]

Because when you e-mail it, it can no longer link to the source so it will not update and presents the last saved data.