How to Autofill names into a Form from a List

[sorensjp]

Hi,

I'm looking for a VBA Macro solution to replace an "in cell" formula based solution which was created by Richard Buttrey on excelforum.

I would like to take the names from an Advanced Filter List to auto insert into the "Spot Checker" cells based on a User selection of the User Choice.
Spot Checker Cells: E26:E36,E46:E57,E62:E63,J26:J36,J38:J40,J46:J49

My list starting on Q6 on the REPORT Sheet is copy of an Advanced Filter Range located on another sheet. The list will change in size based on Personnel Status (i.e. Aboard, On Leave, TAD)

The goal is for a User to select a name from a list which than become the 'top' of the list and loop through filling the Spot Checker (target) cells. So if a user selects from the the very last cell it than rotated to the top and goes until each target cell is filled. The list used to fill the Spot Checker cells will normally be larger than what is required. I have a formula based solution but would like the user have the ability to change a 'Spot Checker' name manually w/out writing over the formula located in that cell.

SPOT CK MATRIX (V0.55) ArrayQuest (1).xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4						Wk 5 QTR-2-2020			04-May-20 to	10-May-20			Spot Checker	Department	Qual Lvl
5	TRIAD/3MC					ACF (Accountability Confidence Factor)								[ALL]	[ALL QUAL'D]
6	Title	NAME	ASSIGNED	PASS/FAIL		COMMAND:	84.6%						LT BARRETT	Weapons	307
7	CO	CAPT ARMSTRONG	EM02	PASS		KHAKI:	84.6%						LT CHAPMAN	Engineering	306
8	XO	CDR FERGUSON	EA01	PASS		PO1/LPO:							LT ELLIS	Combat Systems	305
9	CMC	CDMCM WILSON	CG03	PASS									LT RICHARDSON	Supply	306
10	3MC	MRCM THOMAS	CA02	FAIL	PASS/FAIL	KHAKI	PO1/LPO						LT THOMAS	Combat Systems	306
11			CM	PASS	PASS:	11	0						LTJG FOWLER	Operations	306
12					FAIL:	2	0						LTJG HARPER	Combat Systems	304
13	DEPARTMENT HEADS				Total Spot Cks:	13	0						LTJG HUNT	Weapons	304
14		NAME	ASSIGNED	PASS/FAIL									LTJG MOORE	Weapons	304
15	CBS	LCDR CUNNINGHAM	CF03	PASS		EVV (Equip Validation Verification)							LTJG PHILLIPS	PTO	304
16	WEAPONS	LCDR FOSTER	CG04	PASS									LTJG REED	Exec/Health/Nav	304
17	ENG	LT JOHNSTON	EB14	PASS									LTJG RILEY	Weapons	304
18	SUPPLY	LT ADAMS	S-3	NOT CONDUCTED									LTJG SULLIVAN	Supply	304
19	PTO	LT HARRISON	CSE1	FAIL									LTJG THOMPSON	Combat Systems	304
20	EXEX/NAV	LT HENDERSON	HM01	PASS									ENS ALLEN	Supply	304
21	OPS	LT THOMPSON	OD01	PASS									ENS GIBSON	Operations	304
22													ENS JOHNSTON	Exec/Health/Nav	304
23	COMBAT SYSTEMS DEPARTMENT					WEAPONS DEPARTMENT							ENS MITCHELL	Operations	304
24	SPOT CHECKS		CE/CF/CM	MONITORED MAINTINANCE		SPOT CHECKS		CA/CG/OT Divisions	MONITORED MAINTENANCE				ENS RYAN	Weapons	303
25	NAME	PASS/FAIL		NAME	PASS/FAIL	NAME	PASS/FAIL		NAME	PASS/FAIL			ENS STEVENS	Supply	304
26	ENS ALLEN	PASS	CE01	Zulu38	Zulu39	GSEC MORGAN	Zulu81	CA01	Zulu82	Zulu83			ENS WALKER	Engineering	304
27	ENS GIBSON	PASS	CE02	Zulu42	Zulu43	ETC PERRY	Zulu85	CA02	Zulu86	Zulu87			ENS WRIGHT	Operations	304
28	ENS JOHNSTON	Zulu45	CE03	Zulu46	Zulu47	HMC REED	Zulu89	CG01	Zulu90	Zulu91			CWO3 PHILLIPS	Engineering	303
29	ENS MITCHELL	Zulu49	CSE1	Zulu50	Zulu51	STGC THOMPSON	Zulu93	CG02	Zulu94	Zulu95			CWO2 GRAY	Supply	304
30	ENS RYAN	Zulu53	CSE2	Zulu54	Zulu55	BMC TUCKER	Zulu97	CG03	Zulu98	Zulu99			CWO2 RICHARDS	Engineering	304
31	ENS STEVENS	Zulu57	CF01	Zulu58	Zulu59	LT BARRETT	Zulu101	CG04	Zulu102	Zulu103			FCACM STEVENS	Combat Systems	304
32	ENS WALKER	Zulu61	CF02	Zulu62	Zulu63	LT CHAPMAN	Zulu105	OT01	Zulu106	Zulu107			STGCS CASEY	Weapons	NQ
33	ENS WRIGHT	Zulu65	CF03	Zulu66	Zulu67	LT ELLIS	Zulu109	OT02	Zulu110	Zulu111			EMCS EDWARDS	Combat Systems	NQ
34	CWO3 PHILLIPS	Zulu69	CM01	Zulu70	Zulu71								ETCS MOORE	Combat Systems	304
35	CWO2 GRAY	Zulu73	CM02	Zulu74	Zulu75	EXEC/MED/NAV DEPARTMENT							LSCS PERKINS	Supply	304
36	CWO2 RICHARDS	Zulu77	CM03	Zulu78	Zulu79	SPOT CHECKS		EXEC/HM/ NAV Divisions	MONITORED MAINTENANCE				GSMCS RICHARDS	Engineering	304
37						NAME	PASS/FAIL		NAME	PASS/FAIL			FCAC WARREN	Combat Systems	NQ
38						LT RICHARDSON	Zulu177	EX01	Zulu178	Zulu179			GMCS WEST	Weapons	304
39						LT THOMAS	Zulu181	HM01	Zulu182	Zulu183			CSC ANDERSON	Supply	304
40						LTJG FOWLER	Zulu185	NN01	Zulu186	Zulu187			PSC ANDREWS	Exec/Health/Nav	304
41										.			GMC BARNES	Weapons	304
42	Wk 5 QTR-2-2020						Dates Covered:		04-May-20 to	10-May-20			FCAC FOSTER	Combat Systems	304
43	ENGINEERING DEPARTMENT					SUPPLY DEPARTMENT							MMC HALL	Engineering	NQ
44	SPOT CHECKS		EA/EE/EM/R Divisions	MONITORED MAINTENANCE		SPOT CHECKS		S-1/2/3 Division	MONITORED MAINTENANCE				GSMC MARTIN	Engineering	303
45	NAME	PASS/FAIL		NAME	PASS/FAIL	NAME	PASS/FAIL		NAME	PASS/FAIL			GSEC MORGAN	Engineering	305
46	FCACM STEVENS	Zulu113	EA01	Zulu114	Zulu115	LTJG HARPER	Zulu161	HE01	Zulu162	Zulu163			ETC PERRY	Combat Systems	303
47	STGCS CASEY	Zulu117	EA02	Zulu118	Zulu119	LTJG HUNT	Zulu165	SS01	Zulu166	Zulu167			HMC REED	Exec/Health/Nav	304
48	EMCS EDWARDS	Zulu121	EE01	Zulu122	Zulu123	LTJG MOORE	Zulu169	SS02	Zulu170	Zulu171			STGC THOMPSON	Weapons	304
49	ETCS MOORE	Zulu125	EE02	Zulu126	Zulu127	LTJG PHILLIPS	Zulu173	SS03	Zulu174	Zulu175			BMC TUCKER	Operations	304
50	LSCS PERKINS	Zulu129	EM01	Zulu130	Zulu131
51	GSMCS RICHARDS	Zulu133	EM02	Zulu134	Zulu135	ER09 DIVISION			EVV
52	FCAC WARREN	Zulu137	EM04	Zulu138	Zulu139	DIV	NAME	PASS/FAIL	DIVISION	PASS/FAIL
53	GMCS WEST	Zulu141	EB14	Zulu142	Zulu143	ER09 - CA			CA
54	CSC ANDERSON	Zulu145	FCA1	Zulu146	Zulu147	ER09 - CC			CC
55	PSC ANDREWS	Zulu149	ER01	Zulu150	Zulu151	ER09 - CE			CE
56	GMC BARNES	Zulu153	ER03	Zulu154	Zulu155	ER09 - CF			CF
57	FCAC FOSTER	Zulu157	ER04	Zulu158	Zulu159	ER09 - CM			CM
58						ER09 - EA			EA
59	OPERATIONS DEPARTMENT					ER09 - EE			EE
60	SPOT CHECKS		OI/OD Divisions	MONITORED MAINTENANCE		ER09 - EM			EM
61	NAME	PASS/FAIL		NAME	PASS/FAIL	ER09 - ER			ER
62	MMC HALL	Zulu189	OI01	Zulu190	Zulu191	ER09 - OPS			OI
63	GSMC MARTIN	Zulu193	OD01	Zulu194	Zulu195	ER09 - EXEC			OD
64						ER09 - SUP			EX/MH/NAV
REPORT

Cell Formulas
Range	Formula
J4,E42	J4	=IFERROR(TEXT($J$3," Wk "&"0")&TEXT($F$3," QTR-"&"0")&TEXT($H$3,"-"&"0"),"")
K6	K6	=IFERROR(SUM($J$11:$K$11)/SUM($J$13:$K$13),"")
K7	K7	=IFERROR($J$11/$J$13,"")
K8	K8	=IFERROR($K$11/$K$13,"")
J11	J11	=IFERROR(SUM($AC$10:$AC$17),"")
K11	K11	=IFERROR(SUM($AC$23:$AC$28),"")
J12	J12	=IFERROR(SUM($AD$10:$AD$17),"")
K12	K12	=IFERROR(SUM($AD$23:$AD$28),"")
J13	J13	=IFERROR(SUM($J$11:$J$12),"")
K13	K13	=IFERROR(SUM($K$11:$K$12),"")
J26	J26	=INDEX($Q$6:$Q$49,IF(MATCH(E63,$Q$6:$Q$49,FALSE)+1>=45,1,MATCH(E63,$Q$6:$Q$49,FALSE)+1),1)
E63,E47:E57,J47:J49,J39:J40,E27:E36,J27:J33	J27	=INDEX($Q$6:$Q$49,IF(MATCH(J26,$Q$6:$Q$49,FALSE)+1>=45,1,MATCH(J26,$Q$6:$Q$49,FALSE)+1),1)
J38,E62	J38	=INDEX($Q$6:$Q$49,IF(MATCH(J33,$Q$6:$Q$49,FALSE)+1>=45,1,MATCH(J33,$Q$6:$Q$49,FALSE)+1),1)
M42	M42	=$M$4
N42	N42	=$N$4
J46	J46	=INDEX($Q$6:$Q$49,IF(MATCH(J40,$Q$6:$Q$49,FALSE)+1>=45,1,MATCH(J40,$Q$6:$Q$49,FALSE)+1),1)
E46	E46	=INDEX($Q$6:$Q$49,IF(MATCH(E36,$Q$6:$Q$49,FALSE)+1>=45,1,MATCH(E36,$Q$6:$Q$49,FALSE)+1),1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q6:S80	Expression	=AND($Q6<>"",MOD(ROW(),2)=0)	text	NO
Q6:S80	Expression	=AND($Q6<>"",MOD(ROW(),1)=0)	text	NO
S3,X3,X3,R5:S5,W5:X5	Cell Value	="[ALL]"	text	NO
G7:G11,G15:G21	Expression	=$H7="PASS"	text	NO
H7:H11,H15:H21,F26:F36,N26:N33,K38:K40,N38:N40,I26:I36,K26:K33	Cell Value	="PASS"	text	NO
H7:H11,H15:H21,F26:F36,N26:N33,K38:K40,N38:N40,I26:I36,K26:K33	Cell Value	="FAIL"	text	NO
I46:I57,K46:K49,N46:N49,F62:F63,I62:I63,F46:F57	Cell Value	="PASS"	text	NO
I46:I57,K46:K49,N46:N49,F62:F63,I62:I63,F46:F57	Cell Value	="FAIL"	text	NO
G7:G11,G15:G21	Cell Value	contains "ER09"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
S5,X5	List	=Quals
E26	List	=$Q$6:$Q$49