External Reference Problem

[Torsini]

Hello...I am having a link problem with two Ex2003 files. One references a cell in another. I use a formula like [filelocation][tab]$R$14 and have tried [filelocation][tab]$R14. The problem happens when a row is inserted above row 14 in the reference workbook. If both files are open, the link works fine and the formula updates to R15. If the one file is closed, when it is opened and UPDATE is selected it does not update correctly, and still points to R14. Am I missing something simple? Thanks. Tony

[Richard Schollar]

Nope - that's the way it works I'm afraid. For Excel to update the references the file with the formula pointing at the other workbook must be open.

An alternative to what you have is to name the cell to which you are pointing (eg call it myCell via Insert>Name>Define) - then if as row is inserted, the named cell will be shifted down a row, and thus your formula will point to the correct row.

Richard

[Torsini]

THANKS! I should have thought of that...but thanks for the quick response.
Tony

QUOTE=RichardSchollar;2045869]Nope - that's the way it works I'm afraid. For Excel to update the references the file with the formula pointing at the other workbook must be open.

An alternative to what you have is to name the cell to which you are pointing (eg call it myCell via Insert>Name>Define) - then if as row is inserted, the named cell will be shifted down a row, and thus your formula will point to the correct row.

Richard[/QUOTE]