Looking to automatically update a 2nd spreadsheet

[Reesa]

I have a spreadsheet that outlines parts in a warehouse. It keeps a record of incoming and outgoing parts so I can track their movements. Column A is set as REORDER or OK to advise if stock needs to be reordered. Column B is the part number, Column C is the description. (see image: parts list)

I have a second spreadsheet (designed and requested to by used by my boss) to send for parts that I need to reorder. I can change formulas or add macros, but not the layout. 2nd Spreadsheet to be filled includes: Column A = Part Name, Column B = Part Number, Column E = reorder amount (See image: reorder form).

Bottom line: I would like to automatically reorder stock back to Maximum Stock Levels on the required Order Form.

I've not had a lot of recent experience using macros, but did many moons ago so I feel confident I could work with them if that is the easiest option. Working out formulas is not my forte.

Any help would be greatly appreciated, thank you in advance.

 

[kweaver]

It would be more helpful if you had data displayed using XL2BB and showed values in one sheet that ultimately would fall into the other sheet.

 

[Reesa]

It would be more helpful if you had data displayed using XL2BB and showed values in one sheet that ultimately would fall into the other sheet.

I'm just looking at how I do this, sorry this is new to me. I have installed the plugin, just need to work out the next part, thanks.

 

[kweaver]

Maybe this will get you started:

Book2
	A	B	C	D	E	F	G	H
1	Reorder	Part #	Item	Make	Equipment		MAX	Current
2	OK	111					2	2
3	OK	222					4	4
4	Reorder	333					6	2
5	Reorder	444					30	5
6	OK	555					2	2
7	Reorder	666					2	1
8	OK	777					2	2
9	OK	888					3	3
10	Reorder	999					3	1
11	Reorder	1110					10	2
PartsList

Cell Formulas
Range	Formula
A2:A11	A2	=IF(G2>H2,"Reorder","OK")

Book2
	A	B	C	D
1	Desc.	Part #	QTY On hand	Order
2		333	2	4
3		444	5	25
4		666	1	1
5		999	1	2
6		1110	2	8
ReorderForm

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(PartsList!B2:B11,PartsList!A2:A11="Reorder")
C2:C6	C2	=FILTER(PartsList!H2:H11,PartsList!A2:A11="Reorder")
D2:D6	D2	=FILTER(PartsList!G2:G11-PartsList!H2:H11,PartsList!A2:A11="Reorder")
Dynamic array formulas.

 

[Reesa]

Belvedere Spare Parts Inventory 2021 MAIN.xls.xlsm
	A	B	C	D	E	F	G	H	I	J	K
25	OK	00428901	PIN ASSY., CASE,A-12 (LONG)	OMC	A15 Head	Shaker		2	2	B2 A2	2
26	OK	00470717	WEAR STRIP,UHMW,3/8 X 6	OMC	OMC Shaker	Shaker		4	4	B2 A2	4
27	OK	889-0248	Repair Kit	WMC	9800	Harvester		6	6	B1 C2	6
28	REORDER	948-271	Impeller Blade 11"	WMC	9800	Harvester		30	21	B1 C3	30
29	OK	899-0119	Pick-up Belt	WMC	9800	Harvester		2	2	BP A2	2
30	OK	898-19	Heavy Duty Almond Chain Kit - Elevator	WMC	9800	Harvester		2	2	BP C2	2
31	OK	894-034	Elevator Drive Roller (not used with bar or tube chain)	WMC	9800	Harvester		3	3	B4 P4	3
32	REORDER	900-151	Pickup Belt Drive Roller	WMC	9800	Harvester		3	2	B4 P4	3
33	OK	00470719	WEAR STRIP,UHMW,1/2 X 6	OMC	OMC Shaker	Shaker		4	4	B2 A2	4
34	OK	27044	Front Seal	JKR	Wedge 10	Trailer		1	1	B4 A2	1
35	OK	60120605	SUSPENSION MOUNT, 1-3/16"	OMC	OMC Shaker	Shaker		25	27	B2 A2	27
36	OK	60203818	3/8" Wearstrip	OMC		Shaker		10	13	B2 A2	10
37	REORDER	60203820	1/2" Wearstrip	OMC	Shockwave	Shaker		12	11	B2 A2	4
Parts

Cell Formulas
Range	Formula
H25:H34,H36	H25	=K25
A25:A37	A25	=IF(H25=0,"REORDER",IF(I25<H25,"REORDER","OK"))
I25:I37	I25	=K25+AP25-GN25

 

[Reesa]

Clicking "Mini Sheet" on the second spreadsheet froze my excel, so I will try your formulas soon. Thank you so much for your speedy response.

 

[Reesa]

Thank you so much, it has worked perfectly And I learnt how to post better next time too!

 

[kweaver]

GREAT. Glad it helped and you picked up something new. Cheers.