Need an equation I cant seem to find anywhere else

[2clacaze]

I work for a powder-coat paint company and I have an inventory log that displays type (Hemlock green, Black 08, etc.) and weight in lbs. of all the stock on hand. This is in an excel workbook across several worksheets, separated by general color (green, black,etc.) on each sheet.
Like so.

Sherwin Williams BK08 52
BK43 5
BK162 14
Plascoat Satin Black 9
Hartford Black 63
Rodda 80% Gloss Black 14

etc. etc. etc.

Every few days, the guys in the back who do the coating will bring me a log sheet that states how much and of what color they used so I can keep the inventory up to date, and know when to order more. What I want to do is add another column in which I can enter the amount of powder they used, or the amount shipped in and it will calculate a current value to update.

i.e. Sherwin Williams BK08 52 3
becomes Sherwin Williams BK08 49
It is usually simple math, but this way it alleviates any errors by trying to fill in information in an automated way.

 

[jtakw]

Hi,

This is probably just something to get you started setting up your log:

Excel 2010
	A	B	C	D	E	F	G	H
1				Usage (lbs)
2	Paint Type	Start Inventory (lbs)	Current Inventory (lbs)	Week 1	Week 2	Week 3	Week 4	Week 5
3	Sherwin Williams BK08	52	47	3	2
4	BK43	5	3		1		1
5	BK162	14	9	2				3
6	Plascoat Satin Black	9	7			2
7	Hartford Black	63	51	4	3		5
8	Rodda 80% Gloss Black	14	10				2	2
Sheet1

Cell Formulas
Range	Formula
C3	=B3-SUM(D3:H3)

C3 formula copied down.

 

[2clacaze]

The worksheets already existed before I became an employee here, in the format listed above. I simply need to add another column with an equation that I can enter in the amount of powder used and it will change the number in the log without having to go in and do math on every single one.

 

[jtakw]

The worksheets already existed before I became an employee here, in the format listed above. I simply need to add another column with an equation that I can enter in the amount of powder used and it will change the number in the log without having to go in and do math on every single one.

Does that mean it does the job for you, or you need additional help?

 

[2clacaze]

I still need additional help. I can't change the entire format of the Excel worksheets, I simply have to come up with a way to change the entries with one more entry. Explained in layout under first post. So that i can work down a column entering powder usage or weight shipped in, and it recalculates the total. All my equations fail because the cell i want to subtract from is also the cell i want to change after entering usage.