Link not updating (OFFSET)

[afiack]

I am trying to use an offset formula that references another spreadsheet. I would prefer not to open the linked spreadsheet to which to formula refers, however the formula is returning a '#VALUE' qwhile the referenced spreadsheet is not open and is corrected when that spreadsheet is openned in the current window. Is there any way for the cell value to be updated without openning the referenced spreadsheet?

[VBA Noob]

Hi,

You can set up an OFFSET function to use a cell in another workbook as the reference point. As long as the other workbook is open, the function works. If you close the referenced workbook the function returns the #VALUE! error.

Attached is a link to sum from closed workbook

http://www.exceltip.com/st/Summing_f...ooks/1067.html

VBA Noob

[dominicb]

Good evening afiack

I believe you can get more functionality working with closed files by using the INDIRECT.EXT function contained in the free add-in MoreFunc available from here :

http://xcell05.free.fr/english/

I've not actually examined using it with the OFFSET function - just thought I'd throw it in as an interesting aside if anyone wants to check it out...

HTH

DominicB