I've searched but cannot find a way to eliminate duplicates in the second validation column. I've viewed several postings for eliminating duplicates in the first tier of the cascading validation, but I haven't found for the second tier.
Here is a subset of my data. Columns A through C are my real data. Column F contains unique values in Column A, which will be used to eliminate duplicates in the validation drop down list.
A B C F
1 Color Last First Blue
2 Blue Adams Bill Red
3 Blue Adams Joe Yellow
4 Red Smith Keith
5 Yellow Skaggs Dan
In a separate area on the same sheet, I have cells with data validation, such as:
A B C
10 Blue Adams Bill
Following is the source info for a list validation:
A10:=IF(B10="",F1:F3,A10)
B10:=IF(C10="",OFFSET(A1,MATCH(A10,A1:A5,0)-1,1,COUNTIF(A1:A5,A10),1),B10)
C10:=OFFSET(B1,MATCH(A10,A1:A5,0)-1+MATCH(B10,OFFSET(A1,MATCH(A10,A1:A5,0)-1,1,COUNTIF(A1:A5,A10),1),0)-1,1,COUNTIF(OFFSET(A1,MATCH(A10,A1:A5,0)-1,1,COUNTIF(A1:A5,A10),1),B10),1)
This works great to eliminate duplicates in the drop down list in cell A10; however, when I have duplicates in column A & B (such as above in row 2 & 3), I get a duplicate "Adams" in my drop down list. Can anyone help me eliminate this duplication in the second tier of my cascading data validation?
I've attached the file below which contains this information.
Thanks, in advance, for your help.
Bookmarks