Drop Down Lost no Duplicate allowed on the Row

[VBA learner ITG]

thank you for taking the time looking at my post.

My question is and how would I achieve the below. As Google hasn’t yielded any solutions other than dependent drop downs. But I only have 1 list and no dependents.

Scenario!
I have 8 drop down columns side by side from A to H on a row.

Each drop down list on a row can only allow unique values selected from a Single List.

If a value is selected in Column A it can’t be selected in B and so forth..

 

[Snakehips]

@VBA learner ITG
Questions that may help to get you a solution.
How many input rows might this be applicable to?
How many items in the list?
How are you currently defining the list? Eg Typed in DV dialogue or as range.
Are you open to using a helper column for list?
Are you open to possibility of a VBA element?

 

[VBA learner ITG]

@VBA learner ITG
Questions that may help to get you a solution.
How many input rows might this be applicable to?
How many items in the list?
How are you currently defining the list? Eg Typed in DV dialogue or as range.
Are you open to using a helper column for list?
Are you open to possibility of a VBA element?

Hi, answers below.

There are currently 100 input rows.
There are 47 items in a list.
List is formatted as a Named Range.
I am open to both helper and VBA

Thank you for your support

 

[Snakehips]

What is the starting input row?

 

[VBA learner ITG]

What is the starting input row?

Row 2 are the header row and row 3 onwards are the values rows

 

[Snakehips]

OK, Hopefully this might help?
Has a small trial named ranges 'FullList' and similar sized'DynamicList'. You will need to properly define to suit your data.
Formula in P2 populates the DynamicList
P2 formula requires the input range row index in Q2 which is auto-populated by the Selection_Change code below.
Paste the code to the sheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A3:H102")) Is Nothing Then Exit Sub
Range("Q2") = Target.Row - 2

End Sub

My test data.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1															FullList	DynamicList	IndexRow
2	Headers														AA	AA	1
3	BB	DD	II	KK	LL	GG	CC	JJ							BB	EE
4															CC	FF
5		EE		GG											DD	HH
6															EE
7					DD										FF
8															GG
9															HH
10															II
11															JJ
12															KK
13			DD			CC									LL
14
15				AA
16
17
18	BB			AA
19
20	CC
21
Sheet10

Cell Formulas
Range	Formula
P2:P5	P2	=FILTER(FullList,NOT(ISNUMBER(MATCH(FullList,INDEX($A3:$H102,$Q$2,),0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FullList	=Sheet10!$O$2:$O$13	P2
MyRange	=Sheet10!$O$2:$O$13	P2

Cells with Data Validation
Cell	Allow	Criteria
A3:H21	List	=DynamicList

HTH

 

[VBA learner ITG]

OK, Hopefully this might help?
Has a small trial named ranges 'FullList' and similar sized'DynamicList'. You will need to properly define to suit your data.
Formula in P2 populates the DynamicList
P2 formula requires the input range row index in Q2 which is auto-populated by the Selection_Change code below.
Paste the code to the sheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A3:H102")) Is Nothing Then Exit Sub
Range("Q2") = Target.Row - 2

End Sub

My test data.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1															FullList	DynamicList	IndexRow
2	Headers														AA	AA	20
3	BB	DD	II	KK	LL	GG	CC	JJ							BB	BB
4															CC	CC
5		EE		GG											DD	DD
6															EE	EE
7					DD										FF	FF
8															GG	GG
9															HH	HH
10															II	II
11															JJ	JJ
12															KK	KK
13			DD			CC									LL	LL
14
15				AA
16
17
18	BB			AA
19
20	CC
21
Sheet10

Cell Formulas
Range	Formula
P2:P13	P2	=FILTER(FullList,NOT(ISNUMBER(MATCH(FullList,INDEX($A3:$H102,$Q$2,),0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FullList	=Sheet10!$O$2:$O$13	P2
MyRange	=Sheet10!$O$2:$O$13	P2

Cells with Data Validation
Cell	Allow	Criteria
A3:H21	List	=DynamicList

HTH

Thank you for sharing your expertise as it’s late here, I will go through this tomorrow and test and implement.

You have my gratitude for taking the time to reply to my questions.

 

[VBA learner ITG]

OK, Hopefully this might help?
Has a small trial named ranges 'FullList' and similar sized'DynamicList'. You will need to properly define to suit your data.
Formula in P2 populates the DynamicList
P2 formula requires the input range row index in Q2 which is auto-populated by the Selection_Change code below.
Paste the code to the sheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A3:H102")) Is Nothing Then Exit Sub
Range("Q2") = Target.Row - 2

End Sub

My test data.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1															FullList	DynamicList	IndexRow
2	Headers														AA	AA	1
3	BB	DD	II	KK	LL	GG	CC	JJ							BB	EE
4															CC	FF
5		EE		GG											DD	HH
6															EE
7					DD										FF
8															GG
9															HH
10															II
11															JJ
12															KK
13			DD			CC									LL
14
15				AA
16
17
18	BB			AA
19
20	CC
21
Sheet10

Cell Formulas
Range	Formula
P2:P5	P2	=FILTER(FullList,NOT(ISNUMBER(MATCH(FullList,INDEX($A3:$H102,$Q$2,),0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
FullList	=Sheet10!$O$2:$O$13	P2
MyRange	=Sheet10!$O$2:$O$13	P2

Cells with Data Validation
Cell	Allow	Criteria
A3:H21	List	=DynamicList

HTH

I have tested and implemented and your solution works as expected and I cannot thank you enough for your expertise on this.

 

[Snakehips]

Great to hear. You are most welcome.