Excel worksheets with cells linking to results in other excel workbooks

Hi,

We have this really large excel workbook that contains worksheets that are linked to many other excel files saved in the server. The reason is because the "many other excel files" are produced by various locations. The data that they are produced are used in various ways in this large consolidated excel workbook. The consolidated workbook does not have the same format as the other excel files because it is created for group perspective instead of location focus.

I notice that even though I have saved the consolidated workbook ahead, when I open it again, the cells will display #REF for those cells that are linked to other files. I need to open all the files before those cells can display the data.

Is there anyway to let the workbook display the numbers without opening all the locations reports. There are a lot of reports to open, approx 20+.

I know I can try not to click the "update links" prompt but the users of consolidated report have the tendenacy to click that to ensure data is updated. Otherwise, they will still be opening all reports to check if the figures are correct-----which is defeated the purpose....