Data Validation Based On Distinct Range

[will78]

Hi all,

First posting on here and I would really appreciate your help.

I have a list of data with repeated values stored in a dynamic range. The range needs to be dynamic because the range may increase later and there may be new values introduced. I want to use a data validation drop down box elsewhere in the workbook for the purposes driving some reports.

Now, I can do most of this without issue. However, I need the dropdown to display only DISTINCT records from the dynamic range. Can I do this without creating another dynamic range just to feed the dropdown? It would be nice not to have to update the data in two places.

I have so far creating a working dynamic range and a dropdown looking at this range, but of course it repeats the values as they repeat in the range.

I hope this makes sense. Do you need me to post my workbook?

Many thanks in advance for your help,
Will

 

[GlennUK]

Can I do this without creating another dynamic range just to feed the dropdown?

You could populate a different range by formulas, to get distinct names from your current dynamic range.

An example of a distinct value formula is this array formula ... entered using Ctrl-Shift-Enter instead of Enter:

=IF(NOT(ISNA(MATCH(0,COUNTIF($B$1:B1,Source_List),0))),INDEX(Source_List,MATCH(0,COUNTIF($B$1:B1,Source_List),0)),"")

... entered in cell B2 in this case. Excel will put curly brackets around the formula when you've entered it, to signify that it's an array formula.