FORMULA THAT HELPS UPDATE STOCK RECORD

[lordmatrix]

Hi everyone,

I have three worksheets, first with an inventory of items, second with incoming items received to stock by date in bulk and the third are outgoing items dispatched from stock in bits daily. In the first worksheet is a column "STOCK" that I want to be auto-filled from the activities of the incomings and outgoings such that when stock reads "0" initially for a particular item say "Cosmetics" and there are "100, 50, 30" units added to stock in the incoming items sheet at different days, the stock should read 180 for cosmetics, and when there's dispatch from the outgoing sheet for "3, 5, 40, 6, 20", the stock should be able to adjust its balance by looking at the two worksheets to update the first worksheet containing inventory of items.

I need help with the right formula, I have tried using the SUMIFS function but it only updates the first record on the list, doesn't go to the next record on the inventory list nor does it take a record of other dispatch made out of stock.

I hope my explanation is clear enough for anyone to help me. I will be very grateful as this will make the job easier and smarter.

Thank you,

-lordmatrix

 

[DataBlake]

can you use XL2BB to post examples of your worksheets and expected results?
i can most likely write you a VBA code that does what you want, but i would need to see the sheets.

 

[lordmatrix]

can you use XL2BB to post examples of your worksheets and expected results?
i can most likely write you a VBA code that does what you want, but i would need to see the sheets.

Below is the code from the main inventory sheet.

Book1
	B	C	D	E	F	G	H	I	J	K
4	SELLER'S NAME	DESCRIPTION	BIN #	LOCATION	UNIT	STOCK	REORDER STOCK	COST	INVENTORY VALUE	REORDER
5	MAGAJI	Clothing	T001	Row 1, slot 1	Each	90	10	₦ 3,000.00	₦ 270,000.00
6	GAMBO	Cosmetics	T003	Row 3, slot 1	Each	100	15	₦ 2,300.00	₦ 230,000.00
7	ABDULLAHI	Electronics	T001	Row 1, slot 1	Box (10 ct)	97	5	₦ 20,000.00	₦ 1,940,000.00
8	MUHAMMED	Garden	T003	Row 3, slot 1	Each	100	10	₦ 500.00	₦ 50,000.00
9	HASSAN	Phones	T001	Row 1, slot 1	Each	100	10	₦ 60,000.00	₦ 6,000,000.00
10	DANKWABO	Furniture	T001	Row 1, slot 1	Box (10 ct)	100	10	₦ 25,000.00	₦ 2,500,000.00
11	SHIRI	Babies	T002	Row 2, slot 1	Each	100	5	₦ 2,000.00	₦ 200,000.00
12	AHMED	Footwear	T002	Row 2, slot 1	Each	100	10	₦ 8,000.00	₦ 800,000.00
13	MUSTAPHA	Babies	T003	Row 3, slot 1	Each	100	30	₦ 4,500.00	₦ 450,000.00
14	ALIKO	Footwear	T003	Row 3, slot 1	Each	100	8	₦ 10,000.00	₦ 1,000,000.00
15	PENSHAK	Home Décor	T002	Row 2, slot 1	Box (10 ct)	100	15	₦ 13,000.00	₦ 1,300,000.00
16	MONDAY	Office Furniture	T002	Row 2, slot 1	Box (10 ct)	100	15	₦ 30,000.00	₦ 3,000,000.00
17	TIBAYA	Grocery	T003	Row 3, slot 1	Each	100	10	₦ 3,700.00	₦ 370,000.00
18	JIMOH	Toys & Games	T001	Row 1, slot 1	Each	100	10	₦ 4,500.00	₦ 450,000.00
19	ONIZE	Jewellery	T002	Row 2, slot 1	Each	100	10	₦ 100,000.00	₦ 10,000,000.00
20	MARAUF	Home & Kitchen	T003	Row 3, slot 1	Box (10 ct)	100	8	₦ 50,000.00	₦ 5,000,000.00
21	JOHNSON	Apps & Games	T001	Row 1, slot 1	Each	100	5	₦ 30,000.00	₦ 3,000,000.00
Inventory List

Cell Formulas
Range	Formula
J5:J21	J5	=[@STOCK]*[@COST]
K5:K21	K5	=IFERROR(IF([@STOCK]<=[@[REORDER STOCK]],1,0),0)
E5:E21	E5	=IFERROR(VLOOKUP([@[BIN '#]],BinLookup,3,FALSE),"")
G5:G21	G5	=SUMIFS(InventoryPickList[@[STOCKED QTY]],InventoryPickList[@[SELLER''S NAME]],[@[SELLER''S NAME]])-SUMIFS(InventoryPickList11[@[PICK QTY]],InventoryPickList11[@[SELLER''S NAME]],[@[SELLER''S NAME]])

Named Ranges
Name	Refers To	Cells
SKULookup	=InventoryList[SELLER''S NAME]	G5:G21

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J5:J21	Other Type	DataBar		NO
K5:K21	Other Type	Icon set		NO
B5:K21	Expression	=$K5=1	text	NO
B5:K21	Expression	="If(blnBinNo=""True"")"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B4	Any value
C4	Any value
E4	Any value
F4	Any value
G4	Any value
H4	Any value
I4	Any value
J4	Any value
K4	Any value
D4	Any value
D5:D21	List	=BinNumber

 

[lordmatrix]

Here's the incoming stock sheet that's used to update the stock at different times.

Book1
	C	D	E	F	G	H	I	J	K
4	DATE	SELLER'S NAME	STOCKED QTY	STOCK	DELIVERY STATUS	ITEM DESCRIPTION	UNIT	BIN #	LOCATION
5	1/1/2020	MAGAJI	100	90	SUCCESSFUL	Clothing	Each	T001	Row 1, slot 1
6	1/2/2020	GAMBO	100	100	FAILED	Cosmetics	Each	T003	Row 3, slot 1
7	1/3/2020	ABDULLAHI	100	97	RETURNED	Electronics	Box (10 ct)	T001	Row 1, slot 1
8	1/4/2020	MUHAMMED	100	100		Garden	Each	T003	Row 3, slot 1
9	1/5/2020	HASSAN	100	100		Phones	Each	T001	Row 1, slot 1
10	1/6/2020	DANKWABO	100	100		Furniture	Box (10 ct)	T001	Row 1, slot 1
11	1/7/2020	SHIRI	100	100		Babies	Each	T002	Row 2, slot 1
12	1/8/2020	AHMED	100	100		Footwear	Each	T002	Row 2, slot 1
13	1/9/2020	MUSTAPHA	100	100		Babies	Each	T003	Row 3, slot 1
14	1/10/2020	ALIKO	100	100		Footwear	Each	T003	Row 3, slot 1
15	1/11/2020	PENSHAK	100	100		Home Décor	Box (10 ct)	T002	Row 2, slot 1
16	1/12/2020	MONDAY	100	100		Office Furniture	Box (10 ct)	T002	Row 2, slot 1
17	1/13/2020	TIBAYA	100	100		Grocery	Each	T003	Row 3, slot 1
18	1/14/2020	JIMOH	100	100		Toys & Games	Each	T001	Row 1, slot 1
19	1/15/2020	ONIZE	100	100		Jewellery	Each	T002	Row 2, slot 1
20	1/16/2020	MARAUF	100	100		Home & Kitchen	Box (10 ct)	T003	Row 3, slot 1
21	1/17/2020	JOHNSON	100	100		Apps & Games	Each	T001	Row 1, slot 1
Incoming Stock

Cell Formulas
Range	Formula
H5:H21	H5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,2,FALSE),"")
I5:I21	I5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,5,FALSE),"")
J5:J21	J5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,3,FALSE),"")
K5:K21	K5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,4,FALSE),"")
F5:F21	F5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,6,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:F74	Expression	=E5>F5	text	NO
G5:G74	Expression	=#REF!>G5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D5:D21	List	=SKULookup
G5:G21	List	=#REF!$A$1:$A$3
C4	Any value
D4	Any value
E4	Any value
F4:G4	Any value
H4	Any value
I4	Any value
J4	Any value
K4	Any value

 

[lordmatrix]

This is the outgoing sheet where items are taken out of stock.

Book1
	C	D	E	F	G	H	I	J	K
4	DATE	SELLER'S NAME	PICK QTY	STOCK	DELIVERY STATUS	ITEM DESCRIPTION	UNIT	BIN #	LOCATION
5	1/1/2020	MAGAJI	10	90	SUCCESSFUL	Clothing	Each	T001	Row 1, slot 1
6	1/2/2020	MAGAJI	12	90	FAILED	Clothing	Each	T001	Row 1, slot 1
7	1/3/2020	ABDULLAHI	3	97	RETURNED	Electronics	Box (10 ct)	T001	Row 1, slot 1
8	1/4/2020	ABDULLAHI	4	97		Electronics	Box (10 ct)	T001	Row 1, slot 1
9	1/5/2020	SHIRI	3	100		Babies	Each	T002	Row 2, slot 1
10	1/6/2020	AHMED	7	100		Footwear	Each	T002	Row 2, slot 1
11	1/7/2020	AHMED	5	100		Footwear	Each	T002	Row 2, slot 1
Outgoing Stock

Cell Formulas
Range	Formula
H5:H11	H5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,2,FALSE),"")
I5:I11	I5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,5,FALSE),"")
J5:J11	J5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,3,FALSE),"")
K5:K11	K5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,4,FALSE),"")
F5:F11	F5	=IFERROR(VLOOKUP([SELLER''S NAME],InventoryList,6,FALSE),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5:F74	Expression	=E5>F5	text	NO
G5:G74	Expression	=#REF!>G5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G5:G11	List	=#REF!$A$1:$A$3
D5:D74	List	=SKULookup
E5:E74	Custom	=E5<=F5
C4	Any value
D4	Any value
E4	Any value
F4:G4	Any value
H4	Any value
I4	Any value
J4	Any value
K4	Any value

 

[DataBlake]

sorry for the late response i was on holiday.
I just want to verify a few things.

So in "Inventory List" you want the column G "Stock" to be dynamic where:
- "Incoming Stock" we look at the delivery status and if its successful then we want to add that stock from E or F? to the stock in "Inventory List"
- same thing for "Outgoing Stock" but we subtract from the stock in "Inventory List"

how are rows being added to incoming and outgoing? is this workbook generated or manually updated?
for matching the inventory to a seller i can just use the seller name & item description?