Data Validation list using XLOOKUP

Jmoucro

New Member
Joined
Sep 28, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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???
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
can you add a helper column?
if so then this would work for you
-------------
Book1
DE
5maryae
6aiaf
7ag
8ah
9ai
10aj
11ak
12al
13am
14an
Sheet2
Cell Formulas
RangeFormula
E5:E14E5=FILTER(Table2[account], D5=Table2[servicerep])
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D5Listsue,anne,betty,mary,becky,megan,kim,britney,alyssa
D6List=$E$5:$E$14

-------------
Book1
ADNO
1Salespersonservicerepaccountacctnumber
2bobsuea1
3bobsueb2
4bobsuec3
5bobsued4
6bobsuee5
7bobsuef6
8bobsueg7
9bobsueh8
10bobsuei9
11bobsuej10
12bobannek11
13bobannel12
14bobannem13
15bobannen14
16bobanneo15
17bobannep16
18bobanneq17
19bobanner18
20bobannes19
21bobannet20
22bobbettyu21
23bobbettyv22
24bobbettyw23
25bobbettyx24
26bobbettyy25
27bobbettyz26
28bobbettyaa27
29bobbettyab28
30bobbettyac29
31bobbettyad30
32joemaryae31
33joemaryaf32
34joemaryag33
35joemaryah34
36joemaryai35
37joemaryaj36
38joemaryak37
39joemaryal38
40joemaryam39
41joemaryan40
42joebeckyao41
43joebeckyap42
44joebeckyaq43
45joebeckyar44
46joebeckyas45
47joebeckyat46
48joebeckyau47
49joebeckyav48
50joebeckyaw49
51joebeckyax50
52joemeganay51
53joemeganaz52
54joemeganba53
55joemeganbb54
56joemeganbc55
57joemeganbd56
58joemeganbe57
59joemeganbf58
60joemeganbg59
61joemeganbh60
62joekimbi61
63jimkimbj62
64jimkimbk63
65jimkimbl64
66jimkimbm65
67jimkimbn66
68jimkimbo67
69jimkimbp68
70jimkimbq69
71jimkimbr70
72jimbritneybs71
73jimbritneybt72
74jimbritneybu73
75jimbritneybv74
76jimbritneybw75
77jimbritneybx76
78jimbritneyby77
79jimbritneybz78
80jimbritneyca79
81jimbritneycb80
82jimalyssacc81
83jimalyssacd82
84jimalyssace83
85jimalyssacf84
86jimalyssacg85
87jimalyssach86
88jimalyssaci87
89jimalyssacj88
90jimalyssack89
91jimalyssacl90
Sheet1
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top