Data Validation to Fill in Spreadsheet

[Ebull]

I am creating a battle simulator for a crypto-game that I play (Etheremon if anyone is familar). I am able to pull a list of my possible opponents and all of their "mons" and respective stats using VBA. It looks like this:

Username	Avg BP	Avg Level	Opp. Address	Player ID	Catch Number	Monster ID	Type 1	Type 2	Gason Y/N	Ancestor 1	Ancestor 2	Ancestor 3	Class ID	Total Level	Exp	HP	PA	PD	SA	SD	SPD	Total BP
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	10	43672	1	17	FALSE	80	97	79	137	65	669207	514	702	470	682	275	491	522
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	9	38204	14	6	FALSE	79	97	25	108	85	4487302	637	647	583	591	910	578	657
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	8	37995	9	17	FALSE	97	79	80	157	82	3414717	612	603	859	604	603	614	649
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	258	45054	14	17	FALSE	24	13	32	79	1	1	87	76	92	56	78	62	75
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	128	42677	1	9	FALSE	29	18	16	80	1	1	56	82	82	75	83	69	74
forest gate	609	77	0xc674d5f701bbd8a79315cd2434af18c75816856e	370	166	41949	6		FALSE	32	81	37	97	1	1	67	103	87	52	102	72	80
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	7	29654	4	12	FALSE	25	26	54	92	95	12048271	662	977	972	405	684	661	726
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	2	28096	14	16	FALSE				64	78	2421726	555	312	560	826	812	557	603
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	27	30214	16	12	FALSE	31	28	78	100	81	3266507	579	308	583	842	596	616	587
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	242	26869	4		TRUE				36	1	1	74	53	89	30	97	87	71
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	5749	29912	14	15	FALSE	23	8	24	25	1	86	61	63	67	74	61	74	66
TONKA⇒	638	84	0xa9838c0f833d4e378fc937cc00d8b4080193815b	240	9	30564	12		TRUE				110	1	1	81	45	90	47	78	85	71
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	12	42487	14	6	FALSE	79	97	25	108	53	216923	429	446	402	404	601	378	443
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	6	29098	6	9	FALSE	32	81	37	98	82	3281295	585	839	609	322	594	579	588
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	6	30129	5	6	FALSE	14	27	4	88	83	3602117	602	580	583	861	602	612	640
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	76	48883	6		TRUE				96	1	1	57	25	69	51	78	82	60
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	85	30178	5	6	FALSE	14	4	27	81	1	1	67	75	76	92	80	71	76
¯\_(ツ)_/¯	557	72	0x52e1319ecc564852b63ab200802c49b1d7140f93	55	4677	23209	4	8	FALSE	2	19	4	27	1	1	54	77	59	81	66	74	68
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	28	42610	13	8	FALSE	14	11	16	82	62	511037	478	279	453	651	476	464	466
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	12	36194	18	8	FALSE	15	4	29	71	80	2880461	584	561	560	817	569	578	611
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	7	33932	5	6	FALSE	14	27	4	88	82	3311704	590	591	579	864	583	587	632
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	80	1319	5	16	FALSE				4	2	118	37	45	63	130	70	92	72
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	65	1892	6		FALSE				14	4	490	91	90	141	67	87	74	91
Mon complex	569	74	0x9a81d4da7d0147f0163e4ecf95f67678c40a26a5	67	66	1971	18		FALSE				15	1	13	49	60	87	102	56	87	73
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	3	38900	3	16	FALSE	33	82	5	141	74	1670009	549	552	537	763	540	539	580
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	8	41700	6	17	FALSE	62	87	82	153	71	1217180	521	526	522	747	745	537	599
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	22	30146	16	12	FALSE	31	28	78	100	82	3436479	562	333	602	846	603	604	591
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	36	47399	13	8	FALSE	14	11	16	82	1	37	115	77	102	129	101	105	104
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	52	41854	6		TRUE				96	2	156	79	54	80	43	91	68	69
hagoromo	590	75	0x057dc43af6187a931e78a56b3293912a5b42e5ec	324	3	30545	17		TRUE				112	2	164	64	43	102	45	78	91	70
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	1	26510	5	6	FALSE	14	27	4	88	100	19771257	716	707	687	1012	702	711	755
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	2	29604	16		FALSE				67	88	5985710	603	916	872	334	630	630	664
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	2	38544	2	4	FALSE	90	97	30	133	94	10589235	667	683	654	383	936	935	709
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	8854	41555	4	8	FALSE	2	19	4	27	18	5402	115	183	108	175	110	183	145
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	97	45335	16		TRUE				74	11	2151	109	70	155	81	156	141	118
Lemongrass	709	94	0x3c9c530d684a99cc39388c644f21ebeeb61c2839	705	48	44908	2		TRUE				113	1	1	55	49	88	36	69	58	59
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	4	29126	16	12	FALSE	31	28	78	100	87	5569853	610	349	632	901	635	649	629
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	1	38543	14	12	FALSE	31	99	32	130	63	536866	678	676	473	466	285	476	509
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	9	31911	4	12	FALSE	25	26	54	92	100	18921829	714	###	###	397	703	704	758
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	69	47023	12		TRUE				110	1	1	64	23	71	46	85	83	62
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	114	47024	14		TRUE				76	1	1	67	54	93	25	74	60	62
Mushroom Man	632	83	0x85dc15b8cfc601da2b4cfd2d92da9721cbc2680b	509	257	46384	12		FALSE	14	12	55	31	1	1	69	98	85	64	80	77	78
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	71	49264	2	7	FALSE	33	8	30	148	21	8047	213	196	192	128	123	199	175
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	7	39763	6	17	FALSE	62	87	82	153	69	977652	514	523	508	717	720	525	584
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	3	40377	8	3	FALSE	89	71	70	154	70	1062980	523	529	733	734	536	544	599
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	30	44172	13	8	FALSE	14	11	16	82	43	83343	345	221	354	493	346	361	353
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	46	40432	8		TRUE				109	1	1	58	32	86	36	97	83	65
ももちび0215	452	53	0x798fc047b8628533d70544a24a9ea37f92c88b64	953	27	30776	6		TRUE				96	1	1	64	51	93	33	87	82	68

<colgroup><col><col><col><col><col><col><col><col><col span="5"><col span="2"><col><col span="3"><col><col><col><col></colgroup><tbody>
</tbody>

In another sheet I want to be able to have a dropdown menu where I can choose an opponent's username. For example, if I choose "forest gate" I want a table to be filled in a table like the one below. Mon 1 would be the first "forest gate" mon, Mon 2 the second, and so on: Table shown below.

Username ___
Mon 1		Mon 2		Mon 3		Mon 4		Mon 5		Mon 6
Total Lvl		Total Lvl		Total Lvl		Total Lvl		Total Lvl		Total Lvl
HP		HP		HP		HP		HP		HP
PA		PA		PA		PA		PA		PA
PD		PD		PD		PD		PD		PD
SA		SA		SA		SA		SA		SA
SD		SD		SD		SD		SD		SD
SPD		SPD		SPD		SPD		SPD		SPD
Class ID		Class ID		Class ID		Class ID		Class ID		Class ID
Type 1		Type 1		Type 1		Type 1		Type 1		Type 1
Type 2		Type 2		Type 2		Type 2		Type 2		Type 2
Gason Y/N		Gason Y/N		Gason Y/N		Gason Y/N		Gason Y/N		Gason Y/N

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Ideally I'd like for the drop down to not have duplicates. If I don't use a custom formula I'd see each username 6 times. I'd also like the drop down to be able to show any usernames that appear (not have trouble if VBA imports more than X number of opponents. I'm stuck on how to start this. Any help is greatly appreciated. Thanks in advance.

 

[RasGhul]

You could use this formula option, assuming that your stats tab starts from A1;

Book1
	A	B	C	D	E	F	G
1
2	Player Name
3	forest gate
4
5	Player	Mon 1	Mon 2	Mon 3	Mon 4	Mon 5	Mon 6
6	Total Lvl	65	85	82	1	1	1
7	HP	514	637	612	87	56	67
8	PA	702	647	603	76	82	103
9	PD	470	583	859	92	82	87
10	SA	682	591	604	56	75	52
11	SD	275	910	603	78	83	102
12	SPD	491	578	614	62	69	72
13	Class ID	137	108	157	79	80	97
14	Type 1	1	14	9	14	1	6
15	Type 2	17	6	17	17	9	0
16	Gason Y/N	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Sheet2

Cell Formulas
Range	Formula
B6	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$O$2:$O$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B7	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$Q$2:$Q$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B8	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$R$2:$R$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B9	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$S$2:$S$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B10	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$T$2:$T$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B11	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$U$2:$U$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B12	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$V$2:$V$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B13	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$N$2:$N$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B14	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$H$2:$H$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B15	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$I$2:$I$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
B16	{=IF(COLUMNS(Sheet2!$B$6:B6)>COUNTIF(Sheet1!$A$2:$A$49,Sheet2!$A$3),"",INDEX(Sheet1!$J$2:$J$49,SMALL(IF(Sheet1!$A$2:$A$49=Sheet2!$A$3,ROW(Sheet1!$A$2:$A$49)-ROW(Sheet1!$A$2)+1),COLUMNS(Sheet2!$B$6:B6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.