Excel taking 4 mins to Update Links to another Workbook

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.
meirionwylltSenior Desktop EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jppintoCommented:
One thing to do is to check if there are any broken links that are taking more time to try to update. On Excel 2003 go to Edit-Links.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

there is probably no quick fix to this, other than using a more powerful machine.

How are the links established? Are they part of formulae? Maybe volatile formulae, which will take a big toll on calculation speed?

Check out this article by Charles Williams and then have a look at the nature of the formulae that link to the other file

http://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx

You may find that the solution lies in a redesign of the spreadsheet. Probably not what you want to hear.

cheers, teylyn
0
jppintoCommented:
I would also recommend a new, more powerful computer to help reduce this time. Also, where is the linked file located? On a network drive? Is the user accessing this drive by wireless lan or corded network?

4 minutes to update this amount of links is not, in my opinion, to much. It can be slightly reduced by changing the computer and network conection but I don't believe that you could get this 4 minutes reduced...
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
jppinto, the linked file is on the same network share as the file linking to it. The asker said so in the initial question. :)
0
jppintoCommented:
OK teylyn, the question is so long that I got lost in the middle... :)
0
meirionwylltSenior Desktop EngineerAuthor Commented:
jppinto - I can confirm that there is nothing in the Edit -> Links dialog to suggest that there are broken link.  There is only one file referenced anyway.  Clicking on Check Status gives a status of 'OK'.

Yes, the PC is connected via ethernet cable rather than wireless.

teylyn - I will have a look at the MSDN article.

Regarding the PC's power, another PC that I tried it on was a 2.6GHz Core 2 Duo with 4GB or RAM, Windows 7 with Office 2010 - and this still took 1 min 45 secs.  Another of my test PCs is actually a Citrix Xenapp server, which is an Intel Xeon 2.8GHz 64-bit, Windows Server 2008 R2, and this still takes around 1 mins 45 secs.  It seems that the 1:45 is some kind of lower limit for this spreadsheet.

I don't think this is a network issue, because Excel.exe is taking 99% of the CPU, so CPU seems to be the limiting factor here.

OK, from what you both are saying, I concede that there may not be anything that could be done about the Update speed.

But there is another issue here, namely that there are many fields in the spreadsheet that are blank, even after they were previously Updated and saved.  The impact of this is that there are other cells in the spreadsheet that add them up in a SUM function.  So these cells then become incorrect, because the data that is being summed up is not all there.  This is surely a bug in Excel.

Do you know anything about this problem?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Can you post a few of the formulas that sit in the cells that appear blank?

In the article I linked to above, the author says that linking sheets is a major source of spreadsheet slowness and should be avoided. If you link a 1.5 MB file to a 3MB file, four minutes does not seem all that bad, really. There must be a lot of cells to look up.

Depending on the nature of the links (again, please post some example formulas) this could well be the main reason.

cheers, teylyn
0
meirionwylltSenior Desktop EngineerAuthor Commented:
Hi, here is an example of a formula.  All linking formulae are in this form...

=IF(AND($I20>0.01,$I20<1.001),INDEX('\\server\share\Priffyrdd  Works Instructions\HTMC Rates\[HTMC Rates 2011.xls]Priced'!$J$3:$J$6000,$B20))+IF(AND($I20>=1.009,$I20<=10),INDEX('\\server\share\Priffyrdd  Works Instructions\HTMC Rates\[HTMC Rates 2011.xls]Priced'!$K$3:$K$6000,$B20))+IF(AND($I20>=10.1),INDEX('\\server\share\Priffyrdd  Works Instructions\HTMC Rates\[HTMC Rates 2011.xls]Priced'!$L$3:$L$6000,$B20))

Can you suggest any way that that could be simplified?  Thanks.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

try to open the HTMC Rates 2011.xls workbook, then open the workbook that links to the HTMC Rates 2011.xls file. With the source workbook open, the lookups should be a lot faster.

cheers, teylyn
0
meirionwylltSenior Desktop EngineerAuthor Commented:
Hi, yes I thought that this would improve the times too.

But unfortunately it doesn't affect the link update time at all.

Any ither ideas?

With the fact that there is only one worksheet in one spreadsheet being referenced in all this, is there a way of sort of 'hosting' the source worksheet inside the destination, i.e. is Excel 2010 clever enough that I could drag the worksheet into the destination workbook and have it not copy the sheet, but just provide links to all cells of the source.  Do you know what I mean?  So, in the destination workbook, I would have an extra worksheet, which is a replica of the source worksheet, but not a copy, just a worksheet full of links to the original.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You could try that.

In the destination book, create a new sheet. Have the source workbook open.

In cell A1 of the new sheet, enter a = sign, then click the source workbook cell A1. Copy A1 in the destination sheet across and down as far as required to capture all the data from the source workbook. Close the source workbook.

In the destination book, on the sheet that pulls all the data, run a search and replace and replace all mentions of

'\\server\share\Priffyrdd  Works Instructions\HTMC Rates\[HTMC Rates 2011.xls]Priced'

with the sheet name of the new sheet. Make sure the Find/Replace is looking in Formulas.

Now all the cells link to the new sheet. Save the destination file and close it.

Open the file, and if you are confident that the source file has not changed, do not update the links.

If you know that the source file has changed, then update the links, and be prepared for a wee wait. But if the source file does not change very often, you don't need to update the links every time.

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
meirionwylltSenior Desktop EngineerAuthor Commented:
This method drastically improved the update time.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.