Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    OFFSET() referencing a cell in a linked workbook

    Think I saw something about this a while ago, but I can't find anything when I search. I have a formula =OFFSET() in a workbook where the base cell is in another workbook. The formula evaluates correctly when the linked workbook is also open, but returns an error (#VALUE!) when the linked workbook is closed. What's odd is that everything works fine with both the VLOOKUP() and the MATCH() formulas.
    I'm working in Excel 2000, SR-1.
    I assume it's just something I have to accept and find a work-around for, but am curious why Excel would behave differently with the OFFSET() function.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET() referencing a cell in a linked workbook

    I have had the same experience. I simply have the wkbk open the other wkbk and everything works fine. The 2nd wkbk can be opened in read only mode and it will still work.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET() referencing a cell in a linked workbook

    I have found that whilst VLOOKUP will work with the second workbook closed, things go much faster if it is open.

Posting Permissions

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