Data Validation List - Formula to Sort and Return Unique Values in List

Hello,

I have an external report where the data rows could be updated periodically. What I am looking for is a formula to use in a data validation list to accomplish the following steps:

- First: Be able to lookup in a column in the external data and return a sorted list of unique values (i.e. show only one instance of each repeating value listed in the external data column)

- Second: Depending on the value selected from the drop down in the first step, return sorted values from another column of the external data which corresponds to the value in the first column.

Please see attached sample file for reference.

Thanks,

For Uniique names
=IFERROR(INDEX($A$2:$A$8,SMALL(IF(COUNTIF($C$1:$C1,$A$2:$A$8)=0,ROW($A$2:$A$8),""),1)-ROW($A$2)+1),"")
For Status
=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$E$3,ROW($A$2:$A$8),""),ROW(A1))-ROW($A$2)+1),"")
For validaton of names
=INDIRECT("$C$2:$C$"&COUNTIF($C$2:$C$15,"?*")+1)
For validaton of status
=INDIRECT("$D$2:$D$"&COUNTIF($D$2:$D$15,"?*")+1)

Hi kvsrinivasamurthy,

Thanks for your feedback. While the proposed solution would return the results I am looking for, I was hoping that somehow we can have the unique value incorporated within the data validation and thereby avoid the need for creating the helper columns (in columns C and D) of the sample file. Is this something possible to achieve?

Thanks,

Following up to see if someone could please help me on this issue. After several attempts to modify the proposed formula kvsrinivasamurthy provided, It doesn't seem it will work for my purposes. I am attaching another template with sample data and comments as to what I am trying to accomplish.

I have an external data in one tab which has three columns and where each column may have duplicate values.In another sheet I am trying to utilize data validation list to show the unique values from each column of the external data. This list should available be in each row users will enter.

For instance, the list in column will return the unique values from the third column of the external data tab. The next two column lists will return the unique values that corresponds to the selection in the first column list.

I hope that makes sense.

Thanks in advance!

Any help please.