Drop down list of users name in columns in order to reference a corresponding cell

[sedwardson]

Hopefully the title isn't too confusing but here is my question. I have a small spreadsheet with users names in columns and then a list of mapped drives above the users names. What I would like to do is have a drop down list (or open to another solution) to select a users name and then in the cell to the right of it, tell me which mapped drives that user is entitled to. The user will only appear once in each column so there shouldn't be any duplicates.

Here is my table to demonstrate what I am trying to achieve (obviously names taken out and replaced for example). I would like to drop down the list of users and select an individual and next to it I would like it to tell me what drive letters (mapped drives) need applying to them.

Drive letters	I	M	H, I, J, K, L	H, J, L		Drop Down List of Users	Drives To Be Mapped
Members	User 1	User 19	User 24	User 29
	User 2	User 20	User 25	User 30
	User 3	User 21	User 26	User 31
	User 4	User 22	User 27
	User 5	User 23	User 28
	User 6
	User 7
	User 8
	User 9
	User 10
	User 11
	User 12
	User 13
	User 14
	User 15
	User 16
	User 17
	User 18

Many thanks for all and any help.

Kind regards

Sam

 

[etaf]

you can probably do this a better way

365 version


i would make a column for the users and use
=LET(v,VSTACK(B3:B200,C3:C200,D3:D200,E3:E200),FILTER(v,v<>""))
to create the list for data validation

then use list in datavalidation - choose the range and ignore blanks ticked

then in H3
use
=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))

i have used the range to 200

Book7
	A	B	C	D	E	F	G	H	I	J	K
1	Drive letters	I	M	H, I, J, K, L	H, J, L		Drop Down List of Users	Drives To Be Mapped
2											User 1
3	Members	User 1	User 19	User 24	User 29		User 28	H, I, J, K, L			User 2
4		User 2	User 20	User 25	User 30						User 3
5		User 3	User 21	User 26	User 31						User 4
6		User 4	User 22	User 27							User 5
7		User 5	User 23	User 28							User 6
8		User 6									User 7
9		User 7									User 8
10		User 8									User 9
11		User 9									User 10
12		User 10									User 11
13		User 11									User 12
14		User 12									User 13
15		User 13									User 14
16		User 14									User 15
17		User 15									User 16
18		User 16									User 17
19		User 17									User 18
20		User 18									User 19
21											User 20
22											User 21
23											User 22
24											User 23
25											User 24
26											User 25
27											User 26
28											User 27
29											User 28
30											User 29
31											User 30
32											User 31
33
34
Sheet1

Cell Formulas
Range	Formula
K2:K32	K2	=LET(v,VSTACK(B3:B200,C3:C200,D3:D200,E3:E200),FILTER(v,v<>""))
H3	H3	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	Custom	=LET(v,VSTACK(B3:B20,C3:C20,D3:D20,E3:E20),FILTER(v,v<>""))
G3	List	=$K$2:$K$68

also on share dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[etaf]

you could also use this to lookup the header value
=INDEX($B$1:$E$1,MIN(IF(B2:E25=G3,COLUMN(B2:E25)-COLUMN(B2)+1)))
instead of the aggregate

lookup - header from grid - ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Drive letters	I	M	H, I, J, K, L	H, J, L		Drop Down List of Users	Drives To Be Mapped	OR
2											User 1
3	Members	User 1	User 19	User 24	User 29		User 28	H, I, J, K, L	H, I, J, K, L		User 2
4		User 2	User 20	User 25	User 30						User 3
5		User 3	User 21	User 26	User 31			=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))	=INDEX($B$1:$E$1,MIN(IF(B2:E25=G3,COLUMN(B2:E25)-COLUMN(B2)+1)))		User 4
6		User 4	User 22	User 27							User 5
7		User 5	User 23	User 28							User 6
8		User 6									User 7
9		User 7									User 8
10		User 8									User 9
11		User 9									User 10
12		User 10									User 11
13		User 11									User 12
14		User 12									User 13
15		User 13									User 14
16		User 14									User 15
17		User 15									User 16
18		User 16									User 17
19		User 17									User 18
20		User 18									User 19
21											User 20
22											User 21
23											User 22
24											User 23
25											User 24
26											User 25
27											User 26
28											User 27
29											User 28
30											User 29
31											User 30
32											User 31
33
34
35
TEXT-LU

Cell Formulas
Range	Formula
K2:K32	K2	=LET(v,VSTACK(B3:B200,C3:C200,D3:D200,E3:E200),FILTER(v,v<>""))
H3	H3	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))
I3	I3	=INDEX($B$1:$E$1,MIN(IF(B2:E25=G3,COLUMN(B2:E25)-COLUMN(B2)+1)))
H5:I5	H5	=FORMULATEXT(H3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	Custom	=LET(v,VSTACK(B3:B20,C3:C20,D3:D20,E3:E20),FILTER(v,v<>""))
G3	List	=$K$2:$K$68

 

[sedwardson]

you can probably do this a better way

365 version


i would make a column for the users and use
=LET(v,VSTACK(B3:B200,C3:C200,D3:D200,E3:E200),FILTER(v,v<>""))
to create the list for data validation

then use list in datavalidation - choose the range and ignore blanks ticked

then in H3
use
=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))

i have used the range to 200

Book7
	A	B	C	D	E	F	G	H	I	J	K
1	Drive letters	I	M	H, I, J, K, L	H, J, L		Drop Down List of Users	Drives To Be Mapped
2											User 1
3	Members	User 1	User 19	User 24	User 29		User 28	H, I, J, K, L			User 2
4		User 2	User 20	User 25	User 30						User 3
5		User 3	User 21	User 26	User 31						User 4
6		User 4	User 22	User 27							User 5
7		User 5	User 23	User 28							User 6
8		User 6									User 7
9		User 7									User 8
10		User 8									User 9
11		User 9									User 10
12		User 10									User 11
13		User 11									User 12
14		User 12									User 13
15		User 13									User 14
16		User 14									User 15
17		User 15									User 16
18		User 16									User 17
19		User 17									User 18
20		User 18									User 19
21											User 20
22											User 21
23											User 22
24											User 23
25											User 24
26											User 25
27											User 26
28											User 27
29											User 28
30											User 29
31											User 30
32											User 31
33
34
Sheet1

Cell Formulas
Range	Formula
K2:K32	K2	=LET(v,VSTACK(B3:B200,C3:C200,D3:D200,E3:E200),FILTER(v,v<>""))
H3	H3	=INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	Custom	=LET(v,VSTACK(B3:B20,C3:C20,D3:D20,E3:E20),FILTER(v,v<>""))
G3	List	=$K$2:$K$68

also on share dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Hi @etaf, Thanks for your hard work. I used your first solution (i'll try the second perhaps after you've read this). I have applied it to my spreadsheet and it works ! Unfortunately, I have been given more names to add to the list and there are some duplicate names now in some of the columns. For example I have a few people that are now in both column column B as well as E, and one that is in column column C and D. If you're able to work your magic further I would be very appreciative but I realise that this is probably now a much harder question !

 

[Royalbloodi]

I did things a little differently. I rearranged your data to utilize the INDEX & MATCH functions and Data Validation List more efficiently. I also gave the people names. With this method, you can add people, you can move all your information to a separate hidden sheet, you can do many things. You'd have to adjust a little with your indexing, unless you set up the area with excess.

The Code for the Drives To Be Mapped Column is:

=IFERROR(IF(INDEX($E$1:$E$42, MATCH(A2, $D$1:$D$42, 0))= 0,"", INDEX($E$1:$E$42, MATCH(A2, $D$1:$D$42, 0))),"")

Essentially, this is indexing your list of names, matching them to the selection in the dropdown, taking the value if there is one and returning it to the Drives To Be Mapped cell next to it. If there's no data, it will return a blank. I used the "$" to maintain the code for drag extending the code down.

Hope this helps!

 

[sedwardson]

I did things a little differently. I rearranged your data to utilize the INDEX & MATCH functions and Data Validation List more efficiently. I also gave the people names. With this method, you can add people, you can move all your information to a separate hidden sheet, you can do many things. You'd have to adjust a little with your indexing, unless you set up the area with excess.

View attachment 101281

The Code for the Drives To Be Mapped Column is:

=IFERROR(IF(INDEX($E$1:$E$42, MATCH(A2, $D$1:$D$42, 0))= 0,"", INDEX($E$1:$E$42, MATCH(A2, $D$1:$D$42, 0))),"")

View attachment 101282
Essentially, this is indexing your list of names, matching them to the selection in the dropdown, taking the value if there is one and returning it to the Drives To Be Mapped cell next to it. If there's no data, it will return a blank. I used the "$" to maintain the code for drag extending the code down.

Hope this helps!

Hi @Royalbloodi , Thanks for your reply. Will this work if a user has two sets of mapped drives? Previously I said that there wouldnt be any duplicate names in any of the columns but this is now not the case (see previous entry in thread). Thanks for all your hard work. Sam

 

[Royalbloodi]

Hi @Royalbloodi , Thanks for your reply. Will this work if a user has two sets of mapped drives? Previously I said that there wouldnt be any duplicate names in any of the columns but this is now not the case (see previous entry in thread). Thanks for all your hard work. Sam

I'm sure this could be modified to suit that, but you also could simply just put all the letters for whatever drives they'd need next to their name in the information list. Essentially, consolidate the users names so they don't have more than one data entry. I hope I'm understanding your comment correctly.

 

[Royalbloodi]

I'm sure this could be modified to suit that, but you also could simply just put all the letters for whatever drives they'd need next to their name in the information list. Essentially, consolidate the users names so they don't have more than one data entry. I hope I'm understanding your comment correctly.

I've created a file of some ideas for you. This will allow you to type in your employee's names on an information sheet, it will sort and highlight any duplicates in the next column. At that point you can use your data entry column to delete and consolidate any duplicates. The third column will merge all names of the employee for single-cell reference. On the Lookup Sheet, you have 25ish available and numbered blanks with the dropdown lists for pulling up your employees. It will auto-populate the drives to be mapped to the right of the named list.

Note: In the Information sheet, only modify the first column for adding, modifying and deleting employee names. The other two columns are controlled by Formulae and Conditional formatting.

Download DrivesToBeMapped.xlsx Here.

I hope this helps!

 

[Fluff]

Borrowing etaf's idea how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Drive letters	I	M	H, I, J, K, L	H, J, L		Drop Down List of Users	Drives To Be Mapped	OR
2											User 1
3	Members	User 1	User 19	User 24	User 29		User 28	M, H, I, J, K, L	M, H, I, J, K, L		User 2
4		User 2	User 20	User 25	User 30						User 3
5		User 3	User 21	User 26	User 31						User 4
6		User 4	User 22	User 27							User 5
7		User 5	User 23	User 28							User 6
8		User 6	User 28								User 7
9		User 7									User 8
10		User 8									User 9
11		User 9									User 10
12		User 10									User 11
13		User 11									User 12
14		User 12									User 13
15		User 13									User 14
16		User 14									User 15
17		User 15									User 16
18		User 16									User 17
19		User 17									User 18
20		User 18									User 19
21											User 20
22											User 21
23											User 22
24											User 23
25											User 28
26											User 24
27											User 25
28											User 26
29											User 27
30											User 29
31											User 30
32											User 31
33
Sheet4

Cell Formulas
Range	Formula
K2:K32	K2	=UNIQUE(TOCOL(B3:E100,1,1))
H3	H3	=TEXTJOIN(", ",,INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$200=G3),SEQUENCE(COUNTIFS(B2:E200,G3)))))
I3	I3	=TEXTJOIN(", ",,INDEX($B$1:$E$1,SMALL(IF(B2:E100=G3,COLUMN(B2:E100)-COLUMN(B2)+1),SEQUENCE(COUNTIFS(B2:E100,G3)))))
Dynamic array formulas.

 

[Fluff]

@Royalbloodi please post all solutions to the board, rather than linking to files as per Rule#4 thanks