Selective Data Validation

[apgmin]

Consider the below table
In the first table, adjacent to the subject knowledge 1 name cell, I make the students to choose any from the list of subjects ( Physics... English ). this can be easily done through Data Validation list

the issue is in the next cell, i.e. adjacent to the subject knowledge 2 name cell, If I do the same validation, The student can choose for both the cells say English. This is the issue

I need a formula for the data validation in the second cell such that if the student chooses say the subject English in the first cell, then here he cannot choose English as an option

SUBJECT KNOWLEDGE 1 NAME		PHYSICS​
SUBJECT KNOWLEDGE 2 NAME		CHEMISTRY​
		BIOLOGY​
		GEOGRAPHY​
		HISTORY​
		ECONOMICS​
		MATHS​
		ENGLISH​

Please help

 

[kvsrinivasamurthy]

In D2 copy down up to D9

=IFERROR(INDEX($C$2:$C$9,AGGREGATE(15,6,ROW($C$2:$C$9)/ISERROR(MATCH($C$2:$C$9,$A$2,0)),ROWS($D$2:$D2))-ROW($C$1)),"")

In D1

="Sheet1!D2:D"&1+COUNTIF($D$2:$D$9,"?*")

For DV in A3
Data validation --> List
In Data apply this formula

=INDIRECT(D1)

			Sheet1!D2:D8
PHYSICS		PHYSICS	CHEMISTRY
MATHS		CHEMISTRY	BIOLOGY
		BIOLOGY	GEOGRAPHY
		GEOGRAPHY	HISTORY
		HISTORY	ECONOMICS
		ECONOMICS	MATHS
		MATHS	ENGLISH
		ENGLISH