Results 1 to 7 of 7

Thread: #REF (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    #REF (2000)

    Sometimes when I open a file that contains links to another workbook, I get #REFs in cells where the links are located, but only when the linked workbook is not itself open. Any way to remedy this other than by opening the linked workbook?
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #REF (2000)

    It depends on the formulas/functions used. A simple formula such as

    =[Other.xls]Sheet1!A1

    shouldn't display #REF, but some functions, such as GETPIVOTDATA and INDIRECT only work correctly with external references if the workbook referred to is open.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #REF (2000)

    I think you will also get the dreaded #REF if you have moved the linked workbook.
    IE the error is generated if you specify a path to the linked WBK then move the WBK or imply that the linked WBK is in the same directory as the active WBK by leaving out a specific path, when in reality the linked WBK is not located in the same directory.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #REF (2000)

    If you change calculation to 'manual' before opening the workbook, the #REF shouldn't show up.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: #REF (2000)

    THanks, everyone. Ban, how can I change the workbook to manual calculation before opening? Do you mean before I close it the previous time?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #REF (2000)

    If you set Calculation to manual while your workbook is open, then save it, this setting will be restored when you open the workbook next time.
    But alternatively, you can set Calculation to manual after starting Excel, but before opening the workbook. The setting will persist when you open your workbook.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: #REF (2000)

    Thanks, Hans.

Posting Permissions

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