Adding to a data validation list
 

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 he
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

Adding to a data validation list
 

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 DataValidation
Allow: Custom
Formula:

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

OK out

--
Biff
Microsoft Excel MVP


"Josh Craig" wrote in message
...
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 he
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

Adding to a data validation list
 

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.

--
Regards,

OssieMac


"Josh Craig" wrote:

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 he
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