inventory

F

Fernando

Hello ,I am trying tomake a inventory sheet.Col "A"is my starting total ,Col
"B" is my receiving total, Col "C" is my shipped total and "D "is my Total.

A B C D
5 5 5 5
My receiving total change all the time ,netxt day might be ,received 10
units,no shipping
5 15 0 20 I need a total that keeps adding up.
 
J

JLGWhiz

The formula goes in col D. You can enter it in the first cell in column D
and then drag it down the entire column by clicking on the first cell and
holding while you drag.
 
K

kassie

You are so very right and correct Sir! Serious oversight on my part, thanks
for the correction! And obviously, Col A can also use a formula. Say in A3,
enter the formula =D2, to bring down the total for the previous day.
 
F

Fernando

Next day Rec 10 units 5 10 0 20
Following day Rec 15 Units ship 2 5 15 2 33
I need a total that keeps adding up.by entering a different new receiving
every day will my total will correpond to the new receiving ignoring my last
one. A" stay constant .
Hope this is more clear.
Thanks

 
K

kassie

In that case, I really do not see the need for Col A, as this never gets
added on, except the first day. However, in D1 use the formula =A1+B1-C1
In D2 use the formula =D1+B2-C2. Copy this one down as far as you wish. If
you do not like the column of "0"'s generated, modify the formula in D2 to
read =IF(A2="","",D1+B2-C2). Now, when you enter the 5 in Col A, the total
for that day will appear, and as you add receipts, it will increment, while
shippings wil decrement it
 
F

Fernando

Thank you for your help kassie but ,let me try to explain bit further.Rows 2
,3,4,5 are a different inventory item altogheter .We have only one row to
work with and 2 colums to change constantly B and C,receiving and shipping,my
exemples are from the same row diffrent days entrys.B and C should reset to 0
after entering on total.
Hope this explain better.
thanks again
fernando
 
K

kassie

With all due respect, you then do not need Excel? If it has to reset to 0
every day, then what is the purpose of having a spreadsheet? You will have
no records whatsoever after a month, as you will still have only one days
statistics. You cannot audit, you cannot do stock control, you cannot
establish trends, or do anything useful with that kind of data.

If you say you have a number of products, then your sheet has been set up
incorrectly. If this is the case, I would suggest you use Col A as a date
column, Col B as receipts, C as shipments and D as totals for product 1, Col
E, F and G for product 2 etc.

Use Row 1 to enter the names of the various products. You can merge B1:D1,
E1:G1 etc to make it look nice.
In D2 enter =IF($A2="","",B2-C2). Copy this to Cols G, I, L etc
In D3 enter =IF($A3="","",D2+B3-C3). Copy this across as above, and also
down as far as needs be.
After a month, you will at least have a month's worth of data, which you can
use in so many ways, to establsih trends - Mondays you sell more of item A
then on every other weekday, which means you have to order more for Mondays,
or something to that effect, or that sales on tiem 3 is dwindling, and you
should consider stopping this line - you know......
You will be bale to see how much stock you have, and how your stock
increased and why. You will be able to establish where losses occured. You
name it
 
Top