An user has an Excel Workbook (.xls) of about 1.5MB. She complains that many of the linked cells in her workbook are blank, although they contains formulae. Closer inspection of the workbook shows that there are hundreds if not thousands of links to another workbook (a 3MB file which is on the same local network share as it).
When she opens it, Excel 2003 asks if she wants to update the links. If she clicks Update, then it takes almost four minutes for all the links to update, despite the fact that they are all referencing the same worksheet. If she clicks on 'Don't Update', then many of the cells are blank, even ones with formulae in them. The annoying thing is, even when she goes through the process of Updating links and then saves the Workbook. She might open it another day and a quarter of the cells are blank again - ones that definitely had values in them the last time the workbook was saved.
I realise that updating the links would show the values again, but this is not an option for her because it takes so long. I also realise that an Access database would be more suitable for her needs, but that would be more a long-term answer for her. I'm looking for something short-term at the minute.
One thing to note is that, when Excel is updating, the CPU is hammered at 98%-99% throughout, rendering the PC more or less useless while it happens.
I have tried this with Excel 2010 and the Updating takes only about 2 mins, but I think this is more due to the fact that the Excel 2010 is installed on a much more powerful machine.
I read somewhere that, say for instance you had 500 links to the same wookbook, Excel actually opens and closes the file 500 times to get all the data, rather than opening it once, do 500 lookups, and then close it. If there a way to change this behaviour perhaps?
The user would be happy with one of two solutions - either reduce the amount of time it takes to update, or correct the fault in Excel that shows the cells as blank.
Can anyone shed any light on this issue? Thanks.