I need help with Data Verification and drop down lists

[lambierules]

Hello,

I have an excel sheet I use for a golf leage that I run, I made some changes to the list of team this year copying and pasting into a few of the teams lists which seems to have broken some links. I don't jhave any contact with the person who helped me creat this initally, and due to an incident with my harddrive I have lost all my back up data or else I would start fresh.

The sheets work by selecting the team name from the top of the box thus generating the names for that specific team. 3 of the team lists do not pull up any names when selcted, which I believe is from copying and pasting in the reference page.

Hopefully this is clear and makes sence.

Regards,
Andrew

 

[Skybluekid]

Try this

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		Team A						Player 1		Team A
3		Team B						Player 2
4		Team C						Player 3
5		Team D						Player 4
6		Team E						Player 5
7
8
9
10
11
12		Team A	Team B	Team C	Team D	Team E
13		Player 1	Player 6	Player 11	Player 16	Player 21
14		Player 2	Player 7	Player 12	Player 17	Player 22
15		Player 3	Player 8	Player 13	Player 18	Player 23
16		Player 4	Player 9	Player 14	Player 19	Player 24
17		Player 5	Player 10	Player 15	Player 20	Player 25
18
Sheet4

Cell Formulas
Range	Formula
H2:H6	H2	=OFFSET($A$12,1,MATCH($J$2,B12:F12,0),5)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=OFFSET($A$12,1,MATCH($J$2,B12:F12,0),5)

 

[Alex Blakenburg]

What you looks like a dependent drop down list. It has quite a few moving parts and the easiest would be if you can share your file using a sharing platform such as dropbox, google drive, onedrive etc.

If you want to try yourself first. Click in the one of the player boxes that is not working in City Slickers and click on Data > Data Validation.
(you mention 3 not working but have marked 4 in blue)
Compare what you have in the validation box to what is in the validation box of one of the teams that is working.
I would expect both formula to be referencing the teams line for that team.

Your team player lists look to be in tables. Click inside the table for City Slickers and click on Table Design and see what the table name is in the table name box.

The validation box most likely uses Indirect.
Since your team names have spaces in it the formula will need to do a conversion to use either the Table name or a Named Range.
So if it doesn't look like the Table Name go looking for Named range.

Unfortunately there are several ways of achieving this. The link here will give an idea of the principle.
The video is just under 8 mins.

How to Make Excel Dependent Drop Down Lists - Video Steps

Video shows steps for creating dependent drop down lists on a worksheet. Full transcript and sample file to download

www.contextures.com

 

[lambierules]

What you looks like a dependent drop down list. It has quite a few moving parts and the easiest would be if you can share your file using a sharing platform such as dropbox, google drive, onedrive etc.

If you want to try yourself first. Click in the one of the player boxes that is not working in City Slickers and click on Data > Data Validation.
(you mention 3 not working but have marked 4 in blue)
Compare what you have in the validation box to what is in the validation box of one of the teams that is working.
I would expect both formula to be referencing the teams line for that team.

Your team player lists look to be in tables. Click inside the table for City Slickers and click on Table Design and see what the table name is in the table name box.

The validation box most likely uses Indirect.
Since your team names have spaces in it the formula will need to do a conversion to use either the Table name or a Named Range.
So if it doesn't look like the Table Name go looking for Named range.

Unfortunately there are several ways of achieving this. The link here will give an idea of the principle.
The video is just under 8 mins.

How to Make Excel Dependent Drop Down Lists - Video Steps

Video shows steps for creating dependent drop down lists on a worksheet. Full transcript and sample file to download

www.contextures.com

 

[lambierules]

Hello,

I'm not against sharing the link, this is a little far beyon my knowlege of excel.

Loading Google Sheets

charlies angels
oliver hookers
divot diggers
city slickers.

There is an old team on the reference page the bogie men from when we had more teams, that one wasn't dead so I free typed the names under their banner and it worked, I'm just tired of going up and down to select the names.

Thanks you so much for your assistance@!

 

[Alex Blakenburg]

All the ones with a space in the name are failing. That is because named ranges (including table names) can't have a space in the name.
You will need to go into line 1 of each section and change the existing data validation formula to something that looks like the below.
You can then copy that cell to the other cells for the same team.

ie Oliver Hookers
Heading in G4.
Data validation (Data > Data Validation > Source) needs to be

=INDIRECT(SUBSTITUTE($G$4," ",""))

 

[lambierules]

All the ones with a space in the name are failing. That is because named ranges (including table names) can't have a space in the name.
You will need to go into line 1 of each section and change the existing data validation formula to something that looks like the below.
You can then copy that cell to the other cells for the same team.

ie Oliver Hookers
Heading in G4.
Data validation (Data > Data Validation > Source) needs to be

=INDIRECT(SUBSTITUTE($G$4," ",""))

Thank you! using the code above and moving in around in the difference cells I have gotten it back to working order.

 

[Alex Blakenburg]

Excellent. Thanks for letting us know. Glad we could help.