Excel Order of dependent cells calculation

[SkipVought]

Hi all,

I have a workbook, that is a single user workbook, saved on a company network, that I run several times a day, taking about 6 minutes to complete.

In it, I have a 180 row sheet that uses several lookup functions (INDEX & MATCH) and a database function that accesses 2 databases. That sheet takes about 2 minutes to calculate.

I run this workbook on both my laptop and desktop PC. This sheet has calculated on BOTH machines in the past. Just recently, the sheet calculate has locked up my desktop PC. I know of no changes to my desktop PC.

Any thoughts?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Configure offline files on the desktop. Make relevant files available offline then disconnect from network and try again. Should give you a good idea whether it is the network causing the issue.
I get very erratic results from our network even when all files are open.
One thing that seems to help is to copy key files to a non-network drive. But most reliable is my laptop working with off-line files.

Gavin

 

[SkipVought]

I need the network, because of the UDF database function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Not an explanation but maybe a solution:

Working with large files and many lookups I try to have 1 copy of each formula above each column heading (could also store it in a comment). Code will change calculation to manual, Open linked workbooks, copy the formula for one column at a time, calculate just that column, copy to values, move to next column.
Of course I can't post the code in this forum!


Gavin

 

[Gruuuu]

I've run across a problem that... MAY be related. The difference with my problem was that upon opening the spreadsheet, there would be an error that popped up regarding sharing violations.

The root cause was a user was saving the file while their computer crashed, and the network share still had the file locked for editing by that user, and since the computer stayed on the network, it maintained that status. The only way we solved that problem was turning the computer off long enough for the network share to time out (2 minutes in our case).

My file had no database connections though.