How to keep data cell count info after clearing cells

[vansworld]

Hello,

I am seeking help to continuously track the count of active inventory supply. Currently, a barcode is scanned and it updates a few different tabs with the count of the scan entry. However, I would like to be able to the list where the actual scan takes place without removing the previously scanned and tracked count.

For instance, If I scan 4 SKUs of "COKE-DRIN-CASE-1" it will update the QOH (quantity on hand) and QTY needed columns on the "LIVE Inventory list" tab, the scan will then be added to the list on "Barcode Log Page" as well, and then theres a refreshable pivot built from the "Barcode Log Page" that just tracks the unique scans by SKU.

If I clear out the scans from the "Barcode Scan" page, it will update all the data on the mentioned tabs above.

I am trying to retain the data counts on the above mentioned tabs and be able to clear out the Barcode Scan page; other wise the sheet will get really long. Is this possible?

I am probably intermediate in my excel skills and i'm also using a mac.

Thank you for any advice or help.

BFM Inventory.xlsx
	A	B	C	D	E	F	G	H	I
1	INDEX	ITEM	SKU INFO	CATEGORY	UOM	QOH	QTY NEED	MIN(UOM)	MAX(UOM)
2	1	Coke	COKE-DRIN-CASE-1	Drink	Case	3	7	2	10
3	2	Sprite	SPRI-DRIN-CASE-1	Drink	Case	3	7	2	10
4	3	Water	WATE-DRIN-CASE-1	Drink	Case	3	7	2	10
5	4	Lemonade	LEMO-DRIN-HFGL-1	Drink	hfgl	1	9	1	10
6	5	STR Lemonade	STRL-DRIN-HFGL-1	Drink	hfgl	1	9	1	10
7	6	Peach	PEAC-DRIN-HFGL-1	Drink	hfgl	2	8	1	10
8	7	Blueberry	BLUE-DRIN-HFGL-1	Drink	hfgl	1	9	1	10
9	8	Bacon	BACO-MEAT-POUN-1	Meat	Pound	1	9	3	10
10	9	Catfish	CATF-MEAT-POUN-1	Meat	Pound	1	9	15	10
11	10	Chicken	CHIC-MEAT-POUN-1	Meat	Pound	1	9	10	10
12	11	Crab Meat	CRAB-MEAT-CAN-1	Meat	Can	2	8	4	10
13	12	Lamb	LAMB-MEAT-POUN-1	Meat	Pound	2	8	15	10
14	13	Lobster	LOBS-MEAT-POUN-1	Meat	Pound	2	8	10	10
15	14	Crawfish	CRAW-MEAT-PACK-1	Meat	Pack	1	9	5	10
16	15	Salmon	SALM-MEAT-POUN-1	Meat	Pound	1	9	25	10
17	16	Shrimp (T-On)	STON-MEAT-POUN-1	Meat	Pound	2	8	10	10
18	17	Shrimp (T-Off)	STOF-MEAT-POUN-1	Meat	Pound	2	8	10	10
19	19	Vegetable Oil	VEGE-DRYS-GALL-1	Dry Storage	Gallon	1	9	2	10
20	20	Artesano Buns	ARTE-DRYS-PACK-1	Dry Storage	Pack	1	9	3	10
21	21	Bags	BAGS-DRYS-BOX-1	Dry Storage	Box	1	9	5	10
22	22	Bowls	BOWL-DRYS-BOX-1	Dry Storage	Box	1	9	5	10
23	23	Cutlery	CUTL-DRYS-BOX-1	Dry Storage	Box	1	9	5	10
24	24	Fish Fry	FISH-DRYS-BAG-1	Dry Storage	Bag	2	8	5	10
25	25	Flour	FLOU-DRYS-POUN-1	Dry Storage	Pound	4	6	20	10
26	26	Foil	FOIL-DRYS-ROLL-1	Dry Storage	Roll	2	8	2	10
27	27	Hawaiian Rolls	HAWA-DRYS-PACK-1	Dry Storage	Pack	3	7	10	10
28	28	Honey	HONE-DRYS-BOTT-1	Dry Storage	Bottle	3	7	2	10
29	29	Honey Gold	HONG-DRYS-BOTT-1	Dry Storage	Bottle	6	4	2	10
30	30	Hot Sauce Packets	HOTS-DRYS-BOX-1	Dry Storage	Box	2	8	1	10
31	31	Ketchup	KETC-DRYS-BOX-1	Dry Storage	Box	2	8	1	10
LIVE Inventory List

Cell Formulas
Range	Formula
F2:F31	F2	=COUNTIF('Barcode Scan'!K:K,'LIVE Inventory List'!C2)
G2:G31	G2	=I2-F2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:I91	Expression	=$F2<=$H2	text	NO
A2:I91	Expression	=AND($F2>=$H2,$F2<=$G2)	text	NO
A2:I91	Expression	=$F2>=$I2	text	NO
A2:I91	Expression	=$F2>$G2	text	NO

BFM Inventory.xlsx
	K
1	SCAN BARCODE BELOW
2	COKE-DRIN-CASE-1
3	SPRI-DRIN-CASE-1
4	WATE-DRIN-CASE-1
5	LEMO-DRIN-HFGL-1
6	STRL-DRIN-HFGL-1
7	PEAC-DRIN-HFGL-1
8	BLUE-DRIN-HFGL-1
9	BACO-MEAT-POUN-1
10	CATF-MEAT-POUN-1
11	CHIC-MEAT-POUN-1
12	CRAB-MEAT-CAN-1
13	COKE-DRIN-CASE-1
14	SPRI-DRIN-CASE-1
15	WATE-DRIN-CASE-1
16	SPRI-DRIN-CASE-1
17	COKE-DRIN-CASE-1
18	WATE-DRIN-CASE-1
19	PEAC-DRIN-HFGL-1
20	CRAB-MEAT-CAN-1
21	FISH-DRYS-BAG-1
22	FLOU-DRYS-POUN-1
Barcode Scan

BFM Inventory.xlsx
	A	B
1	BARCODE SCANNED	SCAN QUANTITY
2	COKE-DRIN-CASE-1	1
3	SPRI-DRIN-CASE-1	1
4	WATE-DRIN-CASE-1	1
5	STRL-DRIN-HFGL-1	1
6	STRL-DRIN-HFGL-1	1
7	PEAC-DRIN-HFGL-1	1
8	BLUE-DRIN-HFGL-1	1
9	BACO-MEAT-POUN-1	1
10	CATF-MEAT-POUN-1	1
11	CHIC-MEAT-POUN-1	1
12	CRAB-MEAT-CAN-1	1
13	COKE-DRIN-CASE-1	1
14	SPRI-DRIN-CASE-1	1
15	WATE-DRIN-CASE-1	1
16	SPRI-DRIN-CASE-1	1
17	COKE-DRIN-CASE-1	1
18	WATE-DRIN-CASE-1	1
19	PEAC-DRIN-HFGL-1	1
20	CRAB-MEAT-CAN-1	1
21	FISH-DRYS-BAG-1	1
22	FLOU-DRYS-POUN-1	1
Barcode Log Page (test)

Cell Formulas
Range	Formula
A2:A4,A6:A22	A2	='Barcode Scan'!K2
B2:B22	B2	=COUNTIF('STATIC Inventory List'!C:C,'Barcode Scan'!K:K)
A5	A5	='Barcode Scan'!K6

Named Ranges
Name	Refers To	Cells
SKUS	='STATIC Inventory List'!$C$2:$C$91	B2:B22

BFM Inventory.xlsx
	A	B
3	Row Labels	Sum of SCAN QUANTITY
4	0	0
5	BACO-MEAT-POUN-1	1
6	BLUE-DRIN-HFGL-1	1
7	CATF-MEAT-POUN-1	1
8	CHIC-MEAT-POUN-1	1
9	COKE-DRIN-CASE-1	3
10	CRAB-MEAT-CAN-1	2
11	FISH-DRYS-BAG-1	2
12	FLOU-DRYS-POUN-1	4
13	FOIL-DRYS-ROLL-1	2
14	HAWA-DRYS-PACK-1	3
15	HONE-DRYS-BOTT-1	3
16	HONG-DRYS-BOTT-1	6
17	HOTS-DRYS-BOX-1	2
18	KETC-DRYS-BOX-1	2
19	MOIS-DRYS-BOX-1	2
20	NAPK-DRYS-BOX-1	2
21	PEAC-DRIN-HFGL-1	2
22	PLAT-DRYS-CASE-1	2
23	SPRI-DRIN-CASE-1	3
24	STRL-DRIN-HFGL-1	2
25	WATE-DRIN-CASE-1	3
26	LAMB-MEAT-POUN-1	2
27	LOBS-MEAT-POUN-1	2
28	CRAW-MEAT-PACK-1	1
29	SALM-MEAT-POUN-1	1
30	STON-MEAT-POUN-1	2
31	STOF-MEAT-POUN-1	2
32	ARTE-DRYS-PACK-1	1
33	VEGE-DRYS-GALL-1	1
34	BAGS-DRYS-BOX-1	1
35	CUTL-DRYS-BOX-1	1
36	BOWL-DRYS-BOX-1	1
37	Grand Total	64
Pivot Counter

 

[Herakles]

Do you record stock as it comes in and stock as it goes out?

If so then the QOH should be the difference between the opening stock figure plus stock in minus stock out.

The count of active inventory supply should not be used to maintain accurate stock availability figures but a means of
checking that the figures that are being maintained, using the method described earlier, is working and whether or not stock figures
are being altered (increased or reduced) in other ways e.g. pilfering, unrecorded returns.

If you record activity, stock in and stock out, then you will then be able to calculate what your stock figure was on any particular day.

Worksheets do get really long which is one of the reasons why databases are used for this type of application.

Don't use wanting to keep worksheets small as a reason for doing things in a particular way.