Adding to a data validation list

[Josh Craig]

Hi,

I wanted to have a drop-down list using data validation that would let you
add extra options by entering them. In a previous post I found this solution:

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

However, there is one problem. It now displays duplicates in the drop down
list. Is there any way to use this drop down list and a dynamic name range
but exclude duplicates from the list?

Thanks in advance!

Josh

 

[T. Valko]

Don't enter duplicate items in your source list. You can use data validation
to prevent duplicates.

Suppose the list source is in the range A1:An. This is a dynamic range used
as the source for the drop down.

Select a range of cells big enough to allow for future additions. Let's
assume you select the range Range A1:A100.

Goto Data>Validation
Allow: Custom
Formula:

=COUNTIF(A$1:A1,A1)<2

OK out

 

[OssieMac]

Hi Josh,

You could use data validation to prevent a user from adding a duplicate name
to the list.

Assuming that the list is in a column of its own and starts at cell A1.
Select the entire column A.
Select Data Validation.
Select Custom.
Enter the following formula.
=COUNTIF(A:A,A1)<=1
Check box Ignore blanks.

The user will now be restricted to entering unique names.

TIP when applying data validation: When applying data validation, if you
select the entire range to which validation is to be applied and enter the
formula as if applying validation to the first cell only, Excel correctly
applies the formula to the remaining cells in the selected range.