Excel Linking across multiple sheets

[newbie2181]

Hi,
I have inherited a monthly job that involves copying and pasteing a lot of figures from other spreadsheets into one master spreadsheet. I was looking for ways to stop me falling asleep while doing this job and decided that the best solution would be to link the spreadsheets together so that the cells automatically update. I created all the links for example

='N:\COMP BS29 30\200406\[COMP BS29 30.VS4]COMP BS29 30'!$B$55

This works fine as long as the file 'COMP BS29 30.VS4' is open. If the file is closed i get #REF! error. I thougt that the spaces in the file names might be causeing the problem so i took them out but the problem still remains!

In the tools options menu i am set to Automatically calculate is this correct?

Any suggestions on how to get this working will be much appreciated because it will save me a lot of boredom!!

Thanks a lot

Dan

 

[xlbo]

Tools>Options>Edit
Make sure "Ask to update automatic links" is checked
Tools>Options>Calculation
Tick "Save External Link Values"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[newbie2181]

Thanks for the quick reply. I checked both of the tick boxes you mentioned and they were both already ticked!!

Do the files your linking to have to be true XLS spreadsheets? or could they be csv?

 

[xlbo]

Sorry but I can't replicate the issue - what version of excel are you running ?

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[newbie2181]

2002 mate

 

[newbie2181]

It seems to be a problem with the files i'm using i can't replicate the problem with new spreadsheets either. I'll have to have a play about and see what i can come up with!

Thanks for your help!!

 

[xlbo]

might be a corrupted workbook. You could try copying all sheets to a new workbook and seeing if it works there....
Just use SHIFT to select all sheets in the workbook, right click and choose "Move Or Copy"
Choose "Copy" and "New Workbook", save the file and see if that makes a difference

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question