G
Guest
Hello.
My problem is that i have successfully used sumproduct to add up total of my
calling customers, but it gets very slow and takes about 15 seconds before i
can enter another value in another cell. I think some other function might
help in reducing CPU usage. Any ideas?
Sheet1 contains 4 coloumns and about 2000 rows.
A/c No. Balance-new balance-old Helper
35614 400.00 420.00 20.00
34689 620.00 620.00 00.00
64895 107.23 112.23 05.00
and so on 2000 rows of clients in accending order of their names.
Coloumn A above is manual. Coloumn C is manual. Coloumn B is C-D The new
balance.
helper coloumn : =sumproduct(Ac/No "A2" ie. 35614 in sheet2)*(Amount in
Sheet2) Which would give me 20.00. (see below) This is subtracted from 420.00
in sheet1. The left out value is used as Vlooup reference in sheet2. So i can
get a realtime balance of my customers in Balance-new coloumn as soon as i
enter then amount in sheet2.
Sheet2 is current sheet where current entries are entered.
Coloumn "Balance" uses vlookup to lookup "Ac/No" from Sheet2 and looks it up
in sheet1. and then displays the balance-new from sheet1 next to Ac/No.
Sheet2 looks like this. Coloumn A is Vlookup balance-new, ie: new balance.
A B C
Balance A/c No Amount
400.00 65614 10.00
107.23 34895 05.00
400.00 65614 10.00
400.00 65614
C5 is empty right now. If C5 = 35 then A2, A4 and A5 would display 375.00.
300 such random entries here, off 2000 existing customers.
Anything i can do to make this faster? It takes 15 seconds to calculate.
Thanks.
As soon as 20 is entered, it should display 400 under balance, the real time
balance.
My problem is that i have successfully used sumproduct to add up total of my
calling customers, but it gets very slow and takes about 15 seconds before i
can enter another value in another cell. I think some other function might
help in reducing CPU usage. Any ideas?
Sheet1 contains 4 coloumns and about 2000 rows.
A/c No. Balance-new balance-old Helper
35614 400.00 420.00 20.00
34689 620.00 620.00 00.00
64895 107.23 112.23 05.00
and so on 2000 rows of clients in accending order of their names.
Coloumn A above is manual. Coloumn C is manual. Coloumn B is C-D The new
balance.
helper coloumn : =sumproduct(Ac/No "A2" ie. 35614 in sheet2)*(Amount in
Sheet2) Which would give me 20.00. (see below) This is subtracted from 420.00
in sheet1. The left out value is used as Vlooup reference in sheet2. So i can
get a realtime balance of my customers in Balance-new coloumn as soon as i
enter then amount in sheet2.
Sheet2 is current sheet where current entries are entered.
Coloumn "Balance" uses vlookup to lookup "Ac/No" from Sheet2 and looks it up
in sheet1. and then displays the balance-new from sheet1 next to Ac/No.
Sheet2 looks like this. Coloumn A is Vlookup balance-new, ie: new balance.
A B C
Balance A/c No Amount
400.00 65614 10.00
107.23 34895 05.00
400.00 65614 10.00
400.00 65614
C5 is empty right now. If C5 = 35 then A2, A4 and A5 would display 375.00.
300 such random entries here, off 2000 existing customers.
Anything i can do to make this faster? It takes 15 seconds to calculate.
Thanks.
As soon as 20 is entered, it should display 400 under balance, the real time
balance.