Excel Create Data Validation Unique items for dynamic Drop Down List without new Column

Question

My Table A list which grows on data refresh is as follows:

Balham Halfords - P83690
Balham Halfords - P83690
Gloucester & Durham St - P83680
Gloucester & Durham St - P83680

In another sheet, I want data validation on the drop down list to show only:

Balham Halfords - P83690
Gloucester & Durham St - P83680

The trouble is, I don't want to create a distinct list off Table A anywhere in the book, and I want the data validation list to be intelligent to new records coming into to Table A.

Answer 1

If you want to use Helper column approach with formula, try this solution.

if your data is in column A, enter this formula =IFERROR(INDEX($A$2:$A$900, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$900), 0)),"") in cell D2 and drag it down as long as you estimate there will be distinct values. You must enter it using CTRL+SHIFT+ENTER since it is an array formula.

Then use data validation, select list and under source enter this formula =OFFSET(D2,0,0,198-COUNTBLANK(D2:D200),1)

Now whenever new values are added to table, they are also automatically added to your data validation list.