#REF! error when opening 2 workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: #REF! error when opening 2 workbooks

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default #REF! error when opening 2 workbooks

    Hello,

    Workbook A picks up data from workbook B. However, each time I have both spreadsheets open, all my formulas in workbook A will spit out the error #REF !

    Any idea how to stop this from happening? I've been avoiding opening both spreadsheets at the same time in the past but surely there should be another way!

    Any help is much appreciated.

    Nhung

  2. #2
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    440
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Could you post your specific formula.

  3. #3
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,886
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    One common cause of this is that links are established, workbook A is closed, and then workbook B's tabs are renamed. When they're both open, Workbook A is looking for a sheet that doesn't exist anymore, hence the #REF error.

    Try opening workbook A alone and looking at (write down/memorize) the formula in one of the cells that change to #REF . Then close workbook A and open workbook B. Does the reference from workbook A's formula exist in workbook B?
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  4. #4
    New Member
    Join Date
    Aug 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Before opening workbook B
    ='V:\Monthly Reports\[Comrad.xlsx]Jan 17'!$Y5

    After opening workbook B
    ='[Comrad.xlsx]#REF!'!$Y5

  5. #5
    New Member
    Join Date
    Aug 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Hi Oaktree, the workbooks tabs have not been renamed and it reference from workbook A definately still exist in workbook B - until i open both workbooks together, the formula in workbook A changes to pick up #REF !

    I've posted the formulas in my other reply.

  6. #6
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,886
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Workbook A is looking for, but not finding, a tab exactly named "Jan 17" in workbook B.

    Perhaps there is a leading or trailing space in the sheet name so it's " Jan 17" or "Jan 17 " in workbook B?
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Just checked and there's no spaces on either side...

    I'm selecting the cell in workbook B directly when creating the formula so I don't think its a typing error...

  8. #8
    New Member
    Join Date
    Aug 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #REF! error when opening 2 workbooks

    Found the fix in another forum for anyone that comes across the same issue.

    Solution: Go to File > Option > Trust Center. On right side, Click Trust Center Settings. In Trust Center window, on left side, click Protected View then on right side untick Enable Protected View for files originating from the Internet.
    Now my #REF ! problem solved.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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