Restrict cell value to unique value within column.

  • Thread starter Thread starter Hobbieman
  • Start date Start date
H

Hobbieman

I am trying to create a spreadsheet which contains names of people in
column C.

I would like to configure the spreadsheet, so that as I enter names
into the cells in column C, excel gives me an error if the name being
entered already exists in another cell in Column C.

Anyone know how to do that?
 
From the Tools>Options menu on the Edit tab, check the
"Enable AutoComplete for cell values" checkbox.

Now, Excel will try to AutoFill the entry, such that if you have already
typed
"Jane Doe" somewhere in the list and you begin typing "Jan", it will attempt
to fill in "Jane Doe".
If that happens with the name you are trying to enter, press escape and go
on to the next name.
 
Less intrusive would be just to highlight the cell (see bottom of the post
for another suggestion)

Select the column, with C1 as the active Cell

In the menu to

Format=>Conditional Formatting

Change Cell Value is to Formula is

Enter a formula

=Countif($C$1:C1,C1)>1

click the format button and then select the patterns tab, select a color
like Red

then OK out of the dialogs.

Now if you enter a duplicate value, the cell will turn red.

You could do something similar with the Menu Data=>Validation using the
custom formula.

This will put up an error message.
 
Back
Top