Some update, some don’t

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote