Auto Update Inventory Qtys

[Russ At Index]

Hi ,

Wondered if someone could help with the following Formula query i have :

In workshEet1 i have part description Col A, opening balance qty Col B.

In worksheet2 i have part description Col A and qty out as -2 allocated or in as 10 Received in Col C

(Worksheet 1 being the balance on hand , and worksheet2 used for booking in / out qty of the description).

I would like a formula that looks at the description ( they are the same for both sheets ) and either adds or subtracts
the qty entered in workbook 2 from opening balance in workbook 1 Col B into Col C.

Any assistance would be appreciated ,

Many thanks , stay safe this Festive Season ...

Russ.

 

[ExceLoki]

is something like this what you're looking for?
--------

Book1
	A	B
1	part description	opening balance qty
2	aaa	20
3	bbb	20
4	ccc	20
5	ddd	20
6	eee	20
7	fff	20
balance on hand

-------------

Book1
	A	B	C
1	part description	qty in/out	new balance
2	aaa	1	19
3	bbb	2	18
4	ccc	3	17
5	ddd	4	16
6	eee	5	15
7	fff	6	14
booking in-out

Cell Formulas
Range	Formula
C2:C7	C2	=VLOOKUP(A2,'balance on hand'!A:B,2,FALSE)-B2

 

[Russ At Index]

Hi ExceLoki,

Thank you for your prompt reply .

I was hoping to have the new balance formula in column C of the balance on hand tab.
Eg : Part Desc - Opening Balance - BOH

How would the in/out tab handle a receipt to add stock to the balance on hand tab ?

Thanks ,

Russ.

 

[ExceLoki]

thanks for the clarification. this should be more so what you are looking for.
--------

Book1
	A	B	C
1	part description	opening balance	balance on hand
2	aaa	20	19
3	bbb	20	18
4	ccc	20	17
5	ddd	20	16
6	eee	20	15
7	fff	20	14
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=B2-VLOOKUP(A2,Sheet2!A:B,2,FALSE)

--------

Book1
	A	B
1	part description	qty in/out
2	aaa	1
3	bbb	2
4	ccc	3
5	ddd	4
6	eee	5
7	fff	6
Sheet2

 

[Russ At Index]

Thank you ExceLoki , just what was required .

Enjoy the Holidays .

Russ.