Data Validation list using XLOOKUP

[Jmoucro]

Hopefully I’m not overthinking this. Here’s was I have. (I’ll upload a mini-sheet in a few. I have a data table that is 15 or so columns long but has about 2k rows of data.

Export sheet (sheet1)
Column A is my salesperson Name
Column d is cust service rep.
Column N is the name of the account
Column O is a list of account numbers
Every salesperson has anywhere from 20-30 accts row by row, and the salesperson’s name repeats for each acct they have.
Now each customer service rep has 10 or so of these accounts assigned to them. So their name will repeat in the table as well

Now…. On sheet2 I have a list of cust service reps from sheet1 (Dups removed) in a data validation list in D5. IF I select a cust rep name in D5 in D6 I want a data validation list that will give me a list of all accts for that cust service rep. Originally I did an xlookup to find the acct name and I pasted the formula into the data validation “source” box. And I get on acct name only. Not all acct names for the cust service Rep in a list in D6.

Any suggestions???

 

[ExceLoki]

can you add a helper column?
if so then this would work for you
-------------

Book1
	D	E
5	mary	ae
6	ai	af
7		ag
8		ah
9		ai
10		aj
11		ak
12		al
13		am
14		an
Sheet2

Cell Formulas
Range	Formula
E5:E14	E5	=FILTER(Table2[account], D5=Table2[servicerep])
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D5	List	sue,anne,betty,mary,becky,megan,kim,britney,alyssa
D6	List	=$E$5:$E$14

-------------

Book1
	A			D										N	O
1	Salesperson			servicerep										account	acctnumber
2	bob			sue										a	1
3	bob			sue										b	2
4	bob			sue										c	3
5	bob			sue										d	4
6	bob			sue										e	5
7	bob			sue										f	6
8	bob			sue										g	7
9	bob			sue										h	8
10	bob			sue										i	9
11	bob			sue										j	10
12	bob			anne										k	11
13	bob			anne										l	12
14	bob			anne										m	13
15	bob			anne										n	14
16	bob			anne										o	15
17	bob			anne										p	16
18	bob			anne										q	17
19	bob			anne										r	18
20	bob			anne										s	19
21	bob			anne										t	20
22	bob			betty										u	21
23	bob			betty										v	22
24	bob			betty										w	23
25	bob			betty										x	24
26	bob			betty										y	25
27	bob			betty										z	26
28	bob			betty										aa	27
29	bob			betty										ab	28
30	bob			betty										ac	29
31	bob			betty										ad	30
32	joe			mary										ae	31
33	joe			mary										af	32
34	joe			mary										ag	33
35	joe			mary										ah	34
36	joe			mary										ai	35
37	joe			mary										aj	36
38	joe			mary										ak	37
39	joe			mary										al	38
40	joe			mary										am	39
41	joe			mary										an	40
42	joe			becky										ao	41
43	joe			becky										ap	42
44	joe			becky										aq	43
45	joe			becky										ar	44
46	joe			becky										as	45
47	joe			becky										at	46
48	joe			becky										au	47
49	joe			becky										av	48
50	joe			becky										aw	49
51	joe			becky										ax	50
52	joe			megan										ay	51
53	joe			megan										az	52
54	joe			megan										ba	53
55	joe			megan										bb	54
56	joe			megan										bc	55
57	joe			megan										bd	56
58	joe			megan										be	57
59	joe			megan										bf	58
60	joe			megan										bg	59
61	joe			megan										bh	60
62	joe			kim										bi	61
63	jim			kim										bj	62
64	jim			kim										bk	63
65	jim			kim										bl	64
66	jim			kim										bm	65
67	jim			kim										bn	66
68	jim			kim										bo	67
69	jim			kim										bp	68
70	jim			kim										bq	69
71	jim			kim										br	70
72	jim			britney										bs	71
73	jim			britney										bt	72
74	jim			britney										bu	73
75	jim			britney										bv	74
76	jim			britney										bw	75
77	jim			britney										bx	76
78	jim			britney										by	77
79	jim			britney										bz	78
80	jim			britney										ca	79
81	jim			britney										cb	80
82	jim			alyssa										cc	81
83	jim			alyssa										cd	82
84	jim			alyssa										ce	83
85	jim			alyssa										cf	84
86	jim			alyssa										cg	85
87	jim			alyssa										ch	86
88	jim			alyssa										ci	87
89	jim			alyssa										cj	88
90	jim			alyssa										ck	89
91	jim			alyssa										cl	90
Sheet1