Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Thanked 0 Times in 0 Posts

    Links:The OFFSET function bug (2000 SR1)

    The OFFSET worksheet function don't seem to work well when you refer to
    another workbook.

    I have in a cell in workbook B an OFFSET function that refers to
    workbook A like this:


    The problem is that in case the workbook A is not open the cell
    shows #VALUE! instead of wanted value refereed in workbook A.
    Only when I open workbook A the cell shows the corrected value.

    Ofcourse the "update remote references" in calculation tab of the
    option dialog box is already set. And in the edit links dialog the
    update is marked as automatic as it should.

    Is only in this function that I have this problem. Any other
    link inside function work as it should (for example the
    MATCH function) as well as any other direct link
    of the type "=COCS[A.xls]Sheet1'!A1".

    So what's happening here? Is this a bug of Excel
    or, like they say, is by design?


    Also I would like to know if there's a way to override the
    pesky dialog that appears when you open a workbook that
    has remote references on it (that is that has links to another

    I welcome any answers


  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    Re: Links:The OFFSET function bug (2000 SR1)

    I think it's a weakness in Excel's design; the same problem also occurs with =INDIRECT() when referred to an unopened WB.

    Tools, Options, Edit tab, uncheck Ask to Update Automatic Links may help with your second question, but I know for some people it doesn't work as expected, search this Forum to see similar threads.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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