+ Reply to Thread
Results 1 to 12 of 12

"Reference is not valid" on file open

  1. #1
    Registered User
    Join Date
    07-30-2009
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question "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

  2. #2
    Registered User
    Join Date
    10-19-2003
    Location
    Toronto, Canada
    Posts
    4

    Re: "Reference is not valid" on file open

    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

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: "Reference is not valid" on file open

    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...

  4. #4
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,142

    Re: "Reference is not valid" on file open

    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]

  5. #5
    Registered User
    Join Date
    01-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: "Reference is not valid" on file open

    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

  6. #6
    Registered User
    Join Date
    03-10-2005
    MS-Off Ver
    Excel 2013
    Posts
    95

    Re: "Reference is not valid" on file open

    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.

  7. #7
    Registered User
    Join Date
    01-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: "Reference is not valid" on file open

    Koi - I'm familiar with the named ranges (as stated in my original post), and I don't see any #REFs there.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,050

    Re: "Reference is not valid" on file open

    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

  9. #9
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,142

    Re: "Reference is not valid" on file open

    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

  10. #10
    Registered User
    Join Date
    08-05-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    1

    Re: "Reference is not valid" on file open

    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
    *

  11. #11
    Registered User
    Join Date
    07-04-2016
    Location
    Livonia, Michigan
    MS-Off Ver
    2013
    Posts
    1

    Re: "Reference is not valid" on file open

    I want to upload a TEST FILE.xlsm to this forum to get help with... how do I upload?

  12. #12
    Registered User
    Join Date
    12-03-2018
    Location
    Bay Area, California
    MS-Off Ver
    2013
    Posts
    1

    Re: "Reference is not valid" on file open

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1