Data Validation List to show unique values

vegasbaby207

Board Regular
Joined
Nov 13, 2008
Messages
51
Hi,

I regularly use INDIRECT(tablename[fieldname]") as the source formula of a data validation list to provide the end user with a quick way to select a specific row of data.
Previously, the data source has always contained unique values. I am now working with a new data source that will contain duplicates.

Is there a straightforward way to only show unique values in the dropdown list?

The table I am working with - Orders - has a field called ID - which now contains duplicates.
Not sure if it helps, but there is a column called OrderLineNumber in the same Orders table. Sample below...

IDOrderLineNumber
851
852
853
854
861
862
871
881
891
892
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

vegasbaby207

Board Regular
Joined
Nov 13, 2008
Messages
51
Have a look here.
Hi, thanks for the quick reply.
That was one of the options I found before posting here. Unfortunately it doesn't fit the requirements because the list of options is always growing, and the required interim step of having a list of unique items (the yellow bit on their example) won't work because the list is growing every day.
 

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
101
Just identify the range before starting so that you always have the updated range using something like this:

Excel Formula:
=ROW(OFFSET(A1,COUNTA(A:A)-1,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,009
Members
417,062
Latest member
CM214

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top