Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi
Can anyone help me: I wish to create a very simple to use goods in/ out spreadsheet. Each row will be a different part number. Column A = Part Number Column B = Amount of Goods In Column C = Amount of goods out Column D = Current Stock Held What I want it to do is to add up the quantity each time goods arrive in one cell (cumulatively)and then do the opposite when goods go out. Then subtract Column c from Column B to give me a running total (Column D). Anyone know the formula? |
#2
![]() |
|||
|
|||
![]()
If I understand what you want... Excel formulas return results based on the
numbers actually in the spreadsheet. If all your purchases and sales are entered in individual cells Excel can certainly do the math. But it cannot do a running total of amounts in the cells and amounts that used to be in those cells. You'd need a macro to do an 'accumulator'. (But for my money it's better to have a complete history so you can recreate the current total if needed). -- Jim "Marcus1" wrote in message ... | Hi | Can anyone help me: | I wish to create a very simple to use goods in/ out spreadsheet. | | Each row will be a different part number. | | Column A = Part Number | Column B = Amount of Goods In | Column C = Amount of goods out | Column D = Current Stock Held | | What I want it to do is to add up the quantity each time goods arrive in one | cell (cumulatively)and then do the opposite when goods go out. Then subtract | Column c from Column B to give me a running total (Column D). | | Anyone know the formula? | | | |
#3
![]() |
|||
|
|||
![]()
Hi
It's possible through VBA (you have to write worksheets Change event for this), but it will be wise to think about it again! When you accidently type in some wrong number, then there is no way to restore right number without summing all quantities from original documents. Better enter all transactions into separate sheet, with columns Date, PartNumber, Type, Amount (the column Type can have values "In" or "Out"). On other sheet you'll have another table, with columns PartNumber, InStore , where for every PartNumber, the amount for current moment is calculated through formula like =SUMPRODUCT(--(TransactParts=A2),--(TransactType="In"),TransactAmount)-SUMPRODUCT(--(TransactParts=A2),--(TransactType="Out"),TransactAmount) Such design also allows you to create various other reports, p.e. the list of all incoming or outcoming goods for selected month or year. And it is much simpler to designe too. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Marcus1" wrote in message ... Hi Can anyone help me: I wish to create a very simple to use goods in/ out spreadsheet. Each row will be a different part number. Column A = Part Number Column B = Amount of Goods In Column C = Amount of goods out Column D = Current Stock Held What I want it to do is to add up the quantity each time goods arrive in one cell (cumulatively)and then do the opposite when goods go out. Then subtract Column c from Column B to give me a running total (Column D). Anyone know the formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Running total in Excel w/2 columns | Excel Worksheet Functions | |||
timesheet with running total of overtime | Excel Worksheet Functions | |||
Running Total of Random Number | Excel Discussion (Misc queries) | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |