Custom Data Validation drop downs dependent on a cell value

[Chrissy_M]

Hi all, I wish to create custom data validation drop downs for a large data source.
Each row has a unique category that has 6 rating options and these are tabled in a separate sheet of reference data
I have been able to create unique identifiers, based on rating number and combine it with the unique category to populate the description of the rating, no problem.
What I'm struggling with is reversing this logic to provide a custom drop down list of the 6 rating options related only to that unique category.

To put it into perspective, I have nearly 200 unique categories, each with 6 rating options, which are all unique descriptions of the rating.

I'm hoping that I can find a way to get the drop down to recognise which category it is in and only show the 6 rating options relative to that category.

If anyone can help I'd be most appreciative.

Thanks

 

[myall_blues]

Some sample data and a picture of what you're trying to achieve, with an example is always helpful and you're more likely to get an answer.
You could also try looking up 'dependent dropdown list'.

 

[Chrissy_M]

Colum C autofilled based on Column A
Each Position has 6 optional ratings, detailed in second image.
Methodology required to create drop down in H2, dependent on ratings options available dependent on what value appears in column C.
I have thought about using unique identifiers, but not yet finalised this thought process because the dependent tables seemed a bit challenging as some of the criteria score details might have the same value, even different positions have shared values, so I need a way to differentiate 3 x different lists.
Ultimately the criteria selected for the position/aspect being assessed will populate a criteria score (1-6) that relates to the criteria score detail chosen. Hope that makes sense?

 

[myall_blues]

Create a table somewhere as shown below in J1:O7 which will be your validation table, then create named ranges for each column based on the position. For example, named range "_ABC1" covers J2:J7 etc. You can do this quickly by selecting J1:O7, then Formulas->Defined Names group->Create from selection->Check 'Top row' only and click ok.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Position					Criteria Score Details		_ABC1	_ABC2	_ABC3	_ABC4	_ABC5	_ABC6
2			_ABC1							As new condition	ABC2Criteria1	<10%	ABC4Criteria1	ABC5Criteria1	ABC6Criteria1
3			_ABC2							Protective enclosure/coating sound	ABC2Criteria2	<25%	ABC4Criteria2	ABC5Criteria2	ABC6Criteria2
4			_ABC3							ABC1Criteria3	ABC2Criteria3	<50%	ABC4Criteria3	ABC5Criteria3	ABC6Criteria3
5			_ABC4							ABC1Criteria4	ABC2Criteria4	<75%	ABC4Criteria4	ABC5Criteria4	ABC6Criteria4
6			_ABC5							ABC1Criteria5	ABC2Criteria5	>75%	ABC4Criteria5	ABC5Criteria5	ABC6Criteria5
7			_ABC6							ABC1Criteria6	ABC2Criteria6	Failed	ABC4Criteria6	ABC5Criteria6	ABC6Criteria6
8
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
H2:H7	List	=INDIRECT($C2)

Then in H2, for data validation, set Allow to List, and Source as =INDIRECT($C2) and copy it down.

Now when you select the dropdown in H it will show a list based on whatever is in column C on that row.

Note: For reasons I haven't yet determined Excel refused to let me make named ranges called ABC1, ABC2 etc so I had to add the underscore in front. The important thing is that the values for Position exactly match the names of the named ranges.
Edit: ABC1, 2 etc cannot be used for range names because they are also a valid cell reference.

 

[Chrissy_M]

Create a table somewhere as shown below in J1:O7 which will be your validation table, then create named ranges for each column based on the position. For example, named range "_ABC1" covers J2:J7 etc. You can do this quickly by selecting J1:O7, then Formulas->Defined Names group->Create from selection->Check 'Top row' only and click ok.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Position					Criteria Score Details		_ABC1	_ABC2	_ABC3	_ABC4	_ABC5	_ABC6
2			_ABC1							As new condition	ABC2Criteria1	<10%	ABC4Criteria1	ABC5Criteria1	ABC6Criteria1
3			_ABC2							Protective enclosure/coating sound	ABC2Criteria2	<25%	ABC4Criteria2	ABC5Criteria2	ABC6Criteria2
4			_ABC3							ABC1Criteria3	ABC2Criteria3	<50%	ABC4Criteria3	ABC5Criteria3	ABC6Criteria3
5			_ABC4							ABC1Criteria4	ABC2Criteria4	<75%	ABC4Criteria4	ABC5Criteria4	ABC6Criteria4
6			_ABC5							ABC1Criteria5	ABC2Criteria5	>75%	ABC4Criteria5	ABC5Criteria5	ABC6Criteria5
7			_ABC6							ABC1Criteria6	ABC2Criteria6	Failed	ABC4Criteria6	ABC5Criteria6	ABC6Criteria6
8
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
H2:H7	List	=INDIRECT($C2)

Then in H2, for data validation, set Allow to List, and Source as =INDIRECT($C2) and copy it down.
View attachment 114014

Now when you select the dropdown in H it will show a list based on whatever is in column C on that row.

View attachment 114015

Note: For reasons I haven't yet determined Excel refused to let me make named ranges called ABC1, ABC2 etc so I had to add the underscore in front. The important thing is that the values for Position exactly match the names of the named ranges.
Edit: ABC1, 2 etc cannot be used for range names because they are also a valid cell reference.

Hi there, I tested this methodology but had nothing listed in the drop down options and I'm unsure why?
I placed the table reference for positions on a different worksheet as there are approximately 600 position rows each with 6 rating options, so quite a lot.
Do you know why the drop down might be blank?

 

[myall_blues]

Not really without a bit more detail. Are you able to share your workbook (anonymised if necessary) by uploading it to a site such as OneDrive or Dropbox and posting a link?