Hey. I have prepared an excel sheet that records customer data. I have a "total purchase amount" column. I want it to automatically update itself when i print the bill(which is on another sheet). For example a1 refers to total purchase amount of customer xxx. When he/she purchases goods worth say 500$.. I want it to be automatically added to the previous total in a1 and show the new value( previous value+500$) in the same cell... I am explaining so much bcos i am not familiar with the terms used in excel.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Auto-update of value in cell
- Thread starter enxocoebl
- Start date
-
- Tags
- update cell
Hey kevin
i am sorry.. I meant a list of barcodes in the column ab..since it is a table i ll keep adding rows for each customer.
In the if function u forgot to mention the 'value if false' argument. In this case if it matches it should add and if not it should remain the same(ae4).
Kevin i tried ur recall function in the column next to it and it worked( its more complicated than this)
=if($c$4=$ab4,(recall()+$h$4-$i$4),$af4)... This one works..
I tried formatting the cell to 'numbers' but didnt work.
Kevin i have posted another question if i finish that i ll be able to use this thing in my shop.
i am sorry.. I meant a list of barcodes in the column ab..since it is a table i ll keep adding rows for each customer.
In the if function u forgot to mention the 'value if false' argument. In this case if it matches it should add and if not it should remain the same(ae4).
Kevin i tried ur recall function in the column next to it and it worked( its more complicated than this)
=if($c$4=$ab4,(recall()+$h$4-$i$4),$af4)... This one works..
I tried formatting the cell to 'numbers' but didnt work.
Kevin i have posted another question if i finish that i ll be able to use this thing in my shop.
Upvote
0
Excel Facts
Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
kevin lazell
Well-known Member
- Joined
- Jun 2, 2004
- Messages
- 513
hi enxocoebl
so where are we with this, is it working as you want ?
i left off the last bit of the formula as in your instance it was not needed!
cheers
kevin
so where are we with this, is it working as you want ?
i left off the last bit of the formula as in your instance it was not needed!
cheers
kevin
Upvote
0
hey kevin..
this recall function works fine. the problem is it keeps performing the operation again and again. is there a way to limit it to just one time???
example:
total purchase amount in ae5 is to be updated with the bill amount from g5 so i used the following formula
=IF($C$5=$AB5,(RECALL()+$G$5),$AE5)
it works correctly for the first time. however if i edit some other cell in the worksheet it recalculates and puts the final value in the cell ae5. that is not desirable.. is there a way to limit this??
this recall function works fine. the problem is it keeps performing the operation again and again. is there a way to limit it to just one time???
example:
total purchase amount in ae5 is to be updated with the bill amount from g5 so i used the following formula
=IF($C$5=$AB5,(RECALL()+$G$5),$AE5)
it works correctly for the first time. however if i edit some other cell in the worksheet it recalculates and puts the final value in the cell ae5. that is not desirable.. is there a way to limit this??
Upvote
0
kevin lazell
Well-known Member
- Joined
- Jun 2, 2004
- Messages
- 513
hi enxocoebl
remove the recall formula from ae5 and copy /paste the following into sheet module
to do this copy the following code then right click on the sheet tab select view code
and paste into the box that opens up
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("c5") = Range("ab5") Then Range("ae5") = Range("ae5") + Range("g5"): Range("g5") = 0
End Sub
ps
only problem i can see with this is if you need g5 value to be displayed if so maybe you can
use g6 to = g5 format g6 font color to be the same as interior colour and then change
range("g5") to range("g6") in the code
good luck
kevin
remove the recall formula from ae5 and copy /paste the following into sheet module
to do this copy the following code then right click on the sheet tab select view code
and paste into the box that opens up
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("c5") = Range("ab5") Then Range("ae5") = Range("ae5") + Range("g5"): Range("g5") = 0
End Sub
ps
only problem i can see with this is if you need g5 value to be displayed if so maybe you can
use g6 to = g5 format g6 font color to be the same as interior colour and then change
range("g5") to range("g6") in the code
good luck
kevin
Upvote
0
hi kevin,
hey sorry for the late reply.. i ve been working on it for the past few days. thank you very much for the recall function and additional functions and introducing me to the world of macros.
i used both recall function and macro to get it to work. now its working as intended. i used the if function on one cell as a trigger for starting the calculations and then macro to reset so no clicks in between.
wud u happen to know how to reset a page with dynamic ranges???
i used excel magic trick #334 to prepare it.. but with tables so i dont have to print on a huge sheet if theres only a few products. theres a drop down in the barcode list and vlookup for product name and price...i need to know how i can reset the invoice so that the total moves bk to the third line.
hey sorry for the late reply.. i ve been working on it for the past few days. thank you very much for the recall function and additional functions and introducing me to the world of macros.
i used both recall function and macro to get it to work. now its working as intended. i used the if function on one cell as a trigger for starting the calculations and then macro to reset so no clicks in between.
wud u happen to know how to reset a page with dynamic ranges???
i used excel magic trick #334 to prepare it.. but with tables so i dont have to print on a huge sheet if theres only a few products. theres a drop down in the barcode list and vlookup for product name and price...i need to know how i can reset the invoice so that the total moves bk to the third line.
Upvote
0
