# #VALUE! help needed

#### rcmii

##### Board Regular
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)?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
What formulas are you using in file X?

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.

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.

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

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?

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.

Replies
1
Views
233
Replies
3
Views
345
Replies
3
Views
524
Replies
8
Views
372
Replies
5
Views
601

1,219,579
Messages
6,149,105
Members
450,860
Latest member
Kooth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back