# Auto Update Inventory Qtys

#### Russ At Index

##### Well-known Member
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
is something like this what you're looking for?
--------
Book1
AB
1part descriptionopening balance qty
2aaa20
3bbb20
4ccc20
5ddd20
6eee20
7fff20
balance on hand

-------------
Book1
ABC
1part descriptionqty in/outnew balance
2aaa119
3bbb218
4ccc317
5ddd416
6eee515
7fff614
booking in-out
Cell Formulas
RangeFormula
C2:C7C2=VLOOKUP(A2,'balance on hand'!A:B,2,FALSE)-B2

Hi ExceLoki,

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.

thanks for the clarification. this should be more so what you are looking for.
--------
Book1
ABC
1part descriptionopening balancebalance on hand
2aaa2019
3bbb2018
4ccc2017
5ddd2016
6eee2015
7fff2014
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=B2-VLOOKUP(A2,Sheet2!A:B,2,FALSE)

--------
Book1
AB
1part descriptionqty in/out
2aaa1
3bbb2
4ccc3
5ddd4
6eee5
7fff6
Sheet2

Thank you ExceLoki , just what was required .

Enjoy the Holidays .

Russ.

Replies
1
Views
567
Replies
9
Views
357
Replies
3
Views
398
Replies
10
Views
794
Replies
1
Views
300

1,206,711
Messages
6,074,479
Members
446,071
Latest member
gaborfreeman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back