Bob Umlas, Excel MVP;716765 Wrote:

No VBA required.


I suspect it might be, Bob. How otherwise would "if a value is entered
that already exists in the range, the value that already exists would
become empty and the new location would hold the unique value" be done
without it?


Bob Umlas, Excel MVP;716765 Wrote:

No VBA required. Suppose it's column B which yuo want to limit to
unique
values. Select the whole column, use Data/Validation, select Custom from
the
"Allow" dropdown, enter this formula:
=COUNTIF(B:B,B1)=1
and perhaps click the Error Alert tab to supply a message when a
duplicate
value is entered.

"Michael" wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would
hold
the unique value.

I got something sort of working with the selection change event but
it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks
.



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200469

http://www.thecodecage.com/forumz