subtracting value from inventory as it's used

K

kjcramp

How can I calculate total of inventory received, but also make it
reflect what's been used by the "date used" in an another column,
showing an actual Balance on Hand?
 
B

BigPig

Hi kjcramp,

Do you mean like a checkbook register?
If so, and I'm no accountant, but...:
If Sheet1 column A holds 'Dates', B holds 'Received', and C holds 'Used'

Then, in sheet2 you could type:
A1=Total Received
A2=SUM(sheet1!B2:B26)
B1=Total Used
B2=SUM(sheet1!C2:C26)
C1=Balance
C2=A2-B2

You would have to use your own ranges in lieu of what I used.

Hope this helps.
 
K

kjcramp

D5 is total wt of received steel. I would like this cell to
automatically subtract the lbs of steel in column C when a date is
entered into column E, possibly with an "IF" command (?). Don't know if
it can be done without creating a circular reference since the total is
sum of weights in column C.

I appreciate your help.
 
K

kjcramp

TOTAL WT: 15,295
Delivery Date PN # Weight Coil # Date Used
03/27/06 PN8 4,330 6
03/27/06 PN8 4,295 7
03/27/06 PN8 2,370 8
03/27/06 PN8 4,300 9

Again, I would like the total wt of 15295 to be reduced as each coil of
steel is used, hopefully by a simple date entry under Date Used.

Thanks so much! -Kim
 
B

BigPig

Hi kjcramp,

What I thought of was creating another column (F-Weight Used) which would
show the lbs in your weight colum (C), 'if' something was entered into
'E-Date Used' column. Then in the cell that holds the total wt (B1), have a
formula that sums the wt in the 'Weight' column minus the sum of the wt in
the 'F' column (you would have to create it).

Here's my solution, of which there are many:

If a spreadsheet follows your example, meaning:
A1=TOTAL WT
B1= a formula that adds the wt-wt from date used
A2=Delivery Date
A3 on down holds 'Dates'
B2=PN#
B3 on down holds PN#s
E2=Date Used
etc..

Then in cell B1, which holds your total lbs, you could type
=SUM(C3:C6)-SUM(F3:F6)

*Where C3:C6 is the range of weights and F3:F6 is a column that I included
which holds an If statement, ie cell F3 holds:
=IF(E3="","",C3)
*After entering the formula in cell F3, hover the mouse over the cell F3,
towards the bottom right hand corner (look for the +), click and drag to fill
that column to match your other columns. You could further highlight that
specific range (F-Weight Used), and change the color of the font to white so
that you wouldn't see it.

What the formula in the F 'Weight Used' column does is if 'something' is
entered into the E 'Date Used' column, then the weight in column C 'Weight'
will be displayed in the F column, signifying that you entered a 'Date'. And
then, cell B1 is going to sum the total of the 'Weight' column 'C' minus
whatever is in the 'F' column, if you put in 'something' like a Date in the
'E' column.

Hope this helps.
 
K

kjcramp

It's working! I entered the formulas and columns as you suggested, and
may simply keep the column visible or hide it completely. I would no
have known how to enter those formulas, so I really appreciate you
help!!

Thank you, Ki
 
Top