Cells to automatically update - inventory tracking status

[sgray1]

Hi there,

I have setup an inventory tracking sheet to tracking product coming into my warehouse, then being retagged and then dispatched. I am looking for a formula that will update the "status" (column Q of the first sheet) of stock based on different criteria. If stock coming into the warehouse does not have a new packet number assigned (LL Packet number in Column B of the first sheet) then the status will need to be "in". Once a new Packet number is assigned to an existing packet number then the status will need to update to "WIP". Once the new Packet number is moved to a third sheet to show its been dispatched then I need the status to change to "dispatched". I have setup 3 sheets to capture the In, Retag and Dispatch. What's the best solution to have Column Q update automatically to reflect this based on those 3 criteria's?

Thanks in advance.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Packet	LL Packet Number	Old Grade	Treat	Dry	Finish	Width	Thick	Max Length	Old Piece	Freight CM	CM	Lineal	Description	Run	Buy Price	Status
2	PPS09401	LL112217	BOX	UT	KD	BLK	150	25		48	0.72	0.841	224.3	150x25 BOX UT KD BLK	1	350	Dispatched
3	PSE15501	PSE15501	BOX	UT	KD	RS	100	25		144	2.1	2.1	840	100x25 BOX UT KD RS	1	350	Dispatched
4	PSE15504	LL112217	BOX	UT	KD	RS	200	25		0	0.3	0.3	60	200x25 BOX UT KD RS	1	350	Dispatched
5	PPS08201	LL112131	BOX	UT	KD	RS	300	25		16	0.556	0.556	74.1	300x25 BOX UT KD RS	1	350	WIP
6	PPR14022	LL112130	BOX	UT	KD	RS	300	25		18	0.662	0.662	88.3	300x25 BOX UT KD RS	1	350	WIP
7	PSE10543		BOX	UT	KD	RS	300	25		0	0.5	0.5	66.7	300x25 BOX UT KD RS	1	350	IN
8	PSE10585		BOX	UT	KD	RS	300	25		0	0.6	0.6	80	300x25 BOX UT KD RS	1	350	IN
IN

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	LL Docket Number	Description	Run	Grade	Treat	Dry	Finish	Width	Thick	Piece	Freight	Volume	LM	Original Packet	Actual discription	Status
2	LL112133	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	18	0.567	0.567	75.6	PPS09401	300x25 C1 UT KD RS	Dispatched
3	LL112134	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	51	2.065	2.065	275.4	PSE15501	x C1 UT KD RS	Dispatched
4	LL112132	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	48	1.728	1.728	230.4	PSE15504	x C1 UT KD RS	Dispatched
5	LL112131	100x25 C1 UT KD RS	1	C1	UT	KD	RS	100	25	138	1.457	1.457	582.9	PPS08201	x C1 UT KD RS	WIP
6	LL112130	150x25 COL UT KD RS	1	COL	UT	KD	RS	150	25	105	1.805	1.805	481.2	PPR14022	x COL UT KD RS	WIP
Retag

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	LL Docket Number	Description	Run	Grade	Treat	Dry	Finish	Width	Thick	Piece	Freight	Volume	LM	Original Packet	Actual discription
2	LL112133	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	18	0.567	0.567	75.6	PPS09401	300x25 C1 UT KD RS
3	LL112134	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	51	2.065	2.065	275.4	PSE15501	x C1 UT KD RS
4	LL112132	300x25 C1 UT KD RS	1	C1	UT	KD	RS	300	25	48	1.728	1.728	230.4	PSE15504	x C1 UT KD RS
Dispatch

Book1
	A
1	Status
2	IN
3	WIP
4	Dispatched
Status list

 

[EFANYoutube]

This will work if you put in Q2 and fill down.

=IF(B2="","IN",IF(IFERROR(VLOOKUP(B2,Dispatch!A:A,1,0),0)<>0,"Dispatch",IF(IFERROR(VLOOKUP(B2,Retag!A:A,1,0),0)<>0,"WIP","Error")))