links to external workbook do not copy across/down as expected

[nightster]

Hi there,

Any help with this one would be much appreciated!

I have a link to a cell in an external workbook: ='D:\Finance\[cashflow.xlsx]Cashflow'!A1

When I copy that link down or across, the formula updates (i.e. A1 becomes A2 etc) but the link does not work and the value is given as zero.

If I then put my curser into the formula bar and hit enter, the file selector dialogue box appears. If I then select the file (cashflow.xlsx) and hit enter, the formula then gives the correct value.

However, I can't do this for hundreds of cells.

Why doesn't the formula just copy across/down as I would expect it to? Why does the link to the external workbook get broken?

Thanks!

 

[jmacleary]

Hi there. The link isn't being broken, it's just that excel will only correctly evaluate new external links to workbooks that are open. If you open the workbook that is being referenced then do your dragging, the values will update.

 

[nightster]

Hmm. Thanks, but that didn't work unfortunately.
Could there be anything else causing this issue?

 

[jmacleary]

Do you have automatic calculation set off?

 

[nightster]

No, it's set to automatic.

 

[jmacleary]

OK. In Edit Links, is the Update: option set to automatic? If not, click Startup prompt and select the 3rd option. If that doesn't solve it, I'm afraid it's above my knowledge level.