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.

Answer 2

@DasalKalubowila, here is a modification on @KresimirL's answer that may be what you're looking for.

First, create a defined name for your input data. Do this by going to Formulas on the ribbon and then clicking Name Manager under the Defined Names group.

In my example, I called the input data range InputData. The formula I used is

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$501,MATCH("Ω",Sheet1!$A$2:$A$501))

where

• Sheet1 is the name of the worksheet where the input data lives,

• $A$2 is the first cell containing data in your input range (I call this the anchor),

• $A$2:$A$501 is the max area of the column where your data either lives and could potentially live in the future, and

• "Ω" is the Omega letter. You can get this by holding down ALT and pressing 2 then 3 then 4 on the 10-key number pad (it can also be found in the character map application in Windows).

This formula effectively grows or shrinks your range of data based on how many entries exist.

Next, you need to create a helper column. I know this wasn't desired, but it's going to be one of the only/better ways out there. I placed mine on the same worksheet as my Input Data, but you don't have to. The formula I used in E2 is

=IFERROR(INDEX(InputData,MATCH(0,COUNTIF($E$1:$E1,InputData),0)),"")

You'll need to commit this with Ctrl+Shift+Enter as it's an array formula. Then drag that formula down as far as you to. You'll basically want to go down as many rows as you think you'll have unique entries.

I then needed to create one more defined name, which is what will be used under my Data Validation in the next step. I called this new defined name ValidationList (this needs to be scoped to the Workbook). The formula I used for ValidationList is

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$501,COUNTIF(Sheet1!$E$2:$E$501,">*"))

See the notes for InputData above to understand this formula better. The only difference is that instead of MATCH, this formula uses COUNTIF. This is because if your unique values don't yet fill the entire range you dragged your formula down in Column E (in the previous step), using MATCH in the same way was before would end up grabbing a whole bunch of blanks we don't want. COUNTIF therefore only counts those cells that contain a value greater than "*", with asterisk being a wildcard for any character (and "" contains no characters, so it excludes those items).

Now, create your data validation and set it up like such:

Now you should be left with this:

And when you add information to your InputData area, your range of ValidationList should expand to include the newest uniques, which in turn will populate inside your Data Validation area, like such:

I find that this doesn't seem to slow my workbooks down too significantly, but I'd be interested in hearing how it performs in yours.