#VALUE! help needed

rcmii

Board Regular
Joined
Jul 17, 2008
Messages
93
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)?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rcmii

Board Regular
Joined
Jul 17, 2008
Messages
93
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

Board Regular
Joined
Apr 10, 2006
Messages
102
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

Board Regular
Joined
Jul 17, 2008
Messages
93

ADVERTISEMENT

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

Board Regular
Joined
Jul 17, 2008
Messages
93
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

Board Regular
Joined
Apr 10, 2006
Messages
102
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,754
Members
416,202
Latest member
donya ba

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?

Disable AdBlock

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
Top