"Reference is not valid" on file open
I get the error message "reference is not valid" each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.
I have
- a sheet with raw data
- a sheet with pivot tables
- a sheet with a dashboard
- and a simple macro
I don't have any #REF cells either.
Someone has any idea of what could be the problem?
thanks
NA
It is hard to identify what's the cause of the error message, until someone can actually see the worksheet.
Anyhow, there are few things that you may want to check first;
1) If you have created named ranges, check if there are any problems with those named ranges. Specially, in a situation where your current spreadsheet is recycled meaning at one point it was used for some other purpose and just may be the recycled name range is causing the error message.
2) Check the spread sheet with error checking option
3) Check the report filters in your pivot table.
These are just few of the many areas that you may want to check. As I mentioned earlier, without looking at the spread sheet it is very difficult to exactly pin point the cause of the error message.
If you manage to identify and rectify the error please post the solution so someone else can also benefit.
Axim5
Axim5
Hi guys, I've been using Excel for a decade, have the same error, and still can't seem to find the source of this one.
My spreadsheet has 9 or 10 pivot tables linking to good data. My named ranges are all clean, with no REF errors, and I hit "error checking" through each sheet and it still shows up as all clean.
I also have some charts in the back, and I'm thinking the charts may be linking to something that was deleted. This happens often with charts, where you'll put in a series of labels for an axis of the chart, and you'll delete the actual cells with the reference, and the chart will still display fine. Sometimes a chart will link to a range of data, i.e. A1:C4, and you'll delete the entire row B.
I can't send the file because there's too much confidential stuff in it. But I do wish Excel had this error built into the error checking, like the way it does circular references...
haldoueck,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Arlette - Sorry, I was just commenting that I've had this problem for a long time, and was suggesting some possible reasons for the error. It's very hard to find this sort of error, but I provided other possible places to check.
Thanks,
Hal
hi,
it is mention by axim, it causing by wrong name range.. press ctrl+f3 then see if you have something there with #REF..
you can delete that item or fix it.
Koi - I'm familiar with the named ranges (as stated in my original post), and I don't see any #REFs there.
koi, haldoueck:
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
Why continue your private conversation in some one else's thread, even after moderator warnings?
Ben Van Johnson
koi, haldoueck,
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc
At least one of the pivot has still the wrong name range, to fix it click on pivot "options tab" change data source>>rectify the data source here. I was lucky to identify one of the pivot in my hidden worksheet.
cheers!
KT
*my first post
*
I want to upload a TEST FILE.xlsm to this forum to get help with... how do I upload?
I had this problem as well. I realized that the pivot tables were set to refresh automatically. When I send reports, I usually make a separate copy of the file and then delete the data connected to the pivot tables. When I did this and reopened the file, I received this "reference is not valid" message. When I changed the pivot settings to not refresh automatically I no longer received the error. Hope this helps someone else.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks