Waiting for "Calculating Cells" when there is little to calculate

V

v-deocke

In the sheet I am working on now, I initailly had many formulas, but now I
just have one column of v-lookups (about 8 rows), and just the few rows I
temporarily paste data into. Yet pasting a few rows into the sheet, or even
updating one cell, still makes the sheet take a long time to calcuate after
each action. Does anyone have suggestions on how to improve performance /
stop any unnessesary calculation?
 
M

Mexage

There can be two issues here:

Using the Vlookup function with the last parameter set to FALSE will take
considerably longer to execute than with the value set to TRUE. This is
because when you use FALSE, the worst case scenario will look through all of
the values in the lookup table (proportional to n where n is the number of
rows in your table). With the value set to TRUE, it will require you to sort
your lookup table, but it will take a time proportional to log(n) where n is
the number of rows in your table.

But from the data you are describing, it shouldn't take THAT long.

Perhaps you forgot to erase some cells with the formulas, since you stated
that you used to have a lot of formulas before.

I recommend you use the Find feature with an * as criteria. That way you'll
be able to iterate through all cells which have something in them.

:)

Good luck and please vote for this answer if it helped.
 
B

bj

Since you had all of the stuff in the file, have you saved it, closed Excel
and restarted Excel. If you had more than 65, 536 dependencies (don't ask me
to define them) the file will be in every cell automatic recalc for any
change in the document

I would also check for stuff still in the docuement. On each sheet, select
<edit><goto><special> select last cell anf hit enter. if it any further than
the last visible cell, you might have stuff still crunching.
I would also make sure you have deleted the modules as well as the macros
 
Top