Calculations too long

[KRAMER]

Hi Guy's,
I have an inventory list comprising of a goods in and a goods out sheet. I
have implemented calculations to show what items are in stock and what are
delivered. Both sheets have about 4200 rows of info and the calculations take
about 30-60 sec's to complete when you change a cells value. The functions
being used are as follows

Goods Received
col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200)
col L, =SUMIF('GOODS SENT'!$F$7:$F$4200,F7,'GOODS SENT'!$O$7:$O$4200)
col K, =IF(M7+L7=0," ",IF(L7>M7,"OVERSUPPLY",IF(M7>L7,"IN
STOCK",IF(L7>=M7,"DELIVERED","NOT DELIVERED"))))

Goods Sent
col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200)
col L, =SUMIF('GOODS RECEIVED'!$F$7:$F$4200,F7,'GOODS RECEIVED'!$O$7:$O$4200)
col K, =IF(L7=M7,"COMPLETED",IF(M7>L7,"OVERSUPPLY",IF(M7<L7,"INCOMPLETE")))

N.B. Column F is an invoice number and column O is just a value of 1. These
formulas are repeated for each cell from row 7-4200.

I have similar formulas in another workbook but it calculates almost
immediately.
I don't want to do a manual calc.!
Would macros be quicker??

 

[bj]

It sounds as though it is doing a full cell all recalc whenever you change a
cell value. this can be caused because of the number of dependencies
(ipossible relationships between cells because of equations, etc. I do not
know how to determine how many there are.) being over 65,536 in one workbook
and under in the other workbook. When the number of dependencies is low just
those cells affected by the cell being changed is recalced. when it is high
every cell is recalced.

a macro which would do the calculations and paste values in KLM for
changes in J in just one row might do the job. You may have other
dependencies you could get rid of if you have calcs which were done once only
but the equations have been left in place.

Note: when you try to reduce the dependencies, You will have to close the
file, exit excel and restart excel before you can see whether the dlependency
tree is reduced enough for faster recalcs. Excel apparently triggers a flag
when it sees too many dependencies but the flag is not automatically removed.