View Single Post
  #1   Report Post  
KRAMER
 
Posts: n/a
Default Calculations too long

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(L7M7,"OVERSUPPLY",IF(M7L7,"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(M7L7,"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??
--
KRAMER