Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Some update, some don't

    I have a workbook (call it x.xls) that has several links to another workbook (call it "other.xls").

    Some of the links are in the form of a straight assignment, ie
    ='[other.xls]sheet'!cell
    When I open x.xls and get asked if I want to update links and select yes, no problem even if "other" is not open.

    Other links are embedded in an excel function. When this is the OFFSET function, like
    =OFFSET('[other workbook.xls]sheet'!cell,12*year,5)
    I get the #VALUE error if "other" is not open even if I select yes to updating the links. That is, the links ain't updated.

    I also tried embedding the reference to "other" in the INDEX function and that seemed to update the info even if "other" was not open.

    Is there any rationale for this?

    This behavior is in both Excel 97 and 2000.

    Thanks.

    Fred

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some update, some don't

    Hi there,

    I had the same problem as you with the Offset function (but not with external links), and along the line I realised that if I clicked on the relevant cell, pressed F2 and 'Enter', the cell would update. Luckily I didn't have many cells like that, so I started the macro recorder, did the F2-Enter key combination for all the involved cells and then I stopped the macro recorder. I placed the resulting code in 'Thisworkbook', and now every time I open the file, it updates the macro, i.e. the Offset function in the file.
    You could also put it in a module and add a small button and attach the code to it,if you don't want it to update every time you open the file...just a thought...
    I know it's not the best solution, but it does work and in lack of anything better...Btw, I posted the question in the MSExcel forum a couple of months ago, and nobody had any help for me there.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Some update, some don't

    Hi,

    Thks for the tip. Tried it out but it didn't work. It may very well have to do with the fact that the offset is to an external link.

    Fred

Posting Permissions

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