Data Validation & Vloopup

[dsubash]

Hi,

I have a database in Sheet 1. Image below

Dummy.xlsm
	A	B	C	D	E	F	G
1	Sl. No	Location	Name of Employee	Date	Sales	Collected	Due
2	1	Sydney	SELF	01/11/2024	500	500	0
3	2	Sydney	SELF	01/11/2024	2430	2430	0
4	3	Sydney	SELF	01/11/2024	550	550	0
5	4	Sydney	SELF	01/11/2024	350	350	0
92	91	Ireland	TUV	10/11/2024	100	0	100
93	92	Ireland	TUV	10/11/2024	100	0	100
94	93	Ireland	TUV	10/11/2024	100	0	100
139	138	Japan	KLM	12/11/2024	560	560	0
140	139	Japan	KLM	12/11/2024	130	130	0
141	140	Japan	KLM	12/11/2024	60	60	0
142	141	Japan	SELF	13/11/2024	320	320	0
143	142	Japan	SELF	13/11/2024	100	100	0
160	159	Tokyo	TUV	15/11/2024	100	0	100
161	160	Tokyo	TUV	15/11/2024	100	0	100
162	161	Tokyo	TUV	15/11/2024	100	0	100
163	162	Tokyo	TUV	15/11/2024	100	0	100
183	182	Delhi	SELF	18/11/2024	100	0	100
184	183	Delhi	SELF	18/11/2024	70	0	70
185	184	Delhi	OWN	18/11/2024	280	0	280
201	200	Delhi	SELF	18/11/2024	400	0	400
202	201	Ohio	KLM	11/11/2024	1300	1000	0
203	202	New Jersey	AAA	01/11/2024	590	590	0
204	203	New Jersey	AAA	01/11/2024	70	0	70
205	232	Ohio	KKK	11/11/2024	750	750	0
206	233	Ohio	KKK	11/11/2024	100	0	100
207	234	Ohio	KKK	11/11/2024	100	0	100
208	235	Bangkok	DEF	01/11/2024	150	150	0
209	236	Bangkok	DEF	01/11/2024	60	60	0
210	237	Bangkok	DEF	01/11/2024	100	100	0
211	295	Bangalore	DEF	11/11/2024	100	100	0
212	296	Bangalore	DEF	11/11/2024	100	100	0
213	297	Bangalore	DEF	11/11/2024	100	100	0
214	298	Bangalore	DEF	11/11/2024	150	150	0
215	299	Bangalore	DEF	11/11/2024	100	100	0
216	300	Bangalore	DEF	11/11/2024	300	300	0
Daily Transaction

For One criteria, I have used Name Manager in drop down list to select the Location in K1.

In L1 I tried using data validation drop down list with vlookup function. the code is based on the value in K1. I need the corresponding staff names attached to specific location only to be available in the drop down. (Image below)

Dummy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sl. No	Location	Name of Employee	Date	Sales	Collected	Due				Location	Name of Employee
2											I have used Data Validation using Name Manager for Location	I need to use Data Validation List using Vlookup so that only the names attached the the location to be displayed
3
4
5
6
7
8
9
10
11
12
Report

I tried the following code "=VLOOKUP(L2,'Sheet 1'!A2:B5706,2,0)" in Data Validation List option. It returned an error code stating ""The list source must be a delimited list, or a reference to a single row or column.""

Is there any way I can use Vlookup in data validation.

Please suggest.

Thanks in advance
Subash D

 

[kvsrinivasamurthy]

In L1 formula for DV.
Select DV-->list-->enter formula in source

=INDIRECT("D"&INDEX(ROW($D$2:$D$216),MATCH(K1,$C$2:$C$216,0))&":D"&INDEX(ROW($D$2:$D$216),MATCH(K1,$C$2:$C$216,0)+COUNTIF($C$2:$C$216,K1)-1))

 

[dsubash]

Dear Mr. Murthy,

Thanks for your response.

But, sorry, I have missed one point. My database sheet is in sheet 1 and I need the report in sheet 2.

How do i change the code?

 

[kvsrinivasamurthy]

Use Helper cell, say in Report sheet O1. In O1

="'Daily Transaction'!D"&INDEX(ROW('Daily Transaction'!$D$2:$D$216),MATCH(K1,'Daily Transaction'!$C$2:$C$216,0))&":D"&INDEX(ROW('Daily Transaction'!$D$2:$D$216),MATCH(K1,'Daily Transaction'!$C$2:$C$216,0)+COUNTIF('Daily Transaction'!$C$2:$C$216,K1)-1)

For DV In L1 of Report sheet
=INDIRECT(O1)