Unique Values in Data Validation List

Hello all,

I have put together a sample spreadsheet of an issue I am experiencing, but the main goal is to have 2 drop down lists using data validation to only show unique values:

Box 1 - Project 1 or 2. The range is pulling from a range that includes duplicates, so I only want to show these 2 options
Box 2 - Milestone dates as a drop down list, but I want the list to be flexible depending on the value selected in box #1.

*spreadsheet attached*

Thanks for your time on this!

Let me know if you have questions
SB

This can be done, but it only works because the projects are grouped together. I made a named dynamic range called Date_List with the definition:
=OFFSET(Sheet1!$A$12,MATCH(Sheet1!$A$24,Sheet1!$A$12:$A$20,0)-1,1,COUNTIF(Sheet1!$A$12:$A$20,Sheet1!$A$24),1)
I used this name for the validation.

This is one way to do it.

Copy the Projects list to a temporary area off to the side. Apply Data > Remove duplicates. Copy the resulting unique data and paste Tranposed. I did that in L11:M11 of the attached.

Then array enter this formula in L12 and fill down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=IFERROR(INDEX($B$12:$B$20,SMALL(IF(L$11=$A$12:$A$20,ROW($A$12:$A$20)-MIN(ROW($A$12:$A$20))+1),ROWS($12:12))),"")

Then set data validation for A24 to L11:M11 and data validation for B24 with this formula in Source:

Formula:

=INDEX($L$12:$M$18,,MATCH($A$24,$L$11:$M$11,0))

Glad to hear it. It is always gratifying to hear someone is learning new things from this site. You're welcome and thank you for the feedback.

Since this is solved please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.