preventing cell contents from being entered twice?

[Guest]

I am wondering if it is possible to have a flag or error message, when the
same cell contents, is entered into two different cells on a sheet.

This is for a master schedule created in Excel.

Example.

The address "52 chestnut" is entered into a cell and remains there.

Later in the sheet, if "52 chestnut" is entered, some sort of error or flag
or the "#######" message can come up.

I know its a strech, but is it possible?

Regards,

Andy

 

[Guest]

You can use conditional formating. Supposing the area where the data can be
entered is A1:Z1000, with a formula like:
=(COUNTIF($A$1:$Z$1000,A1)>1)
You can select the format to fill in red the cells, for example, and when
you enter the same value twice both cells will show the red color.

Hope this helps,
Miguel.

 

[Guest]

You can use Conditional Formating:


Say you are using columns A thru Z. Start with A1. Select it and pull-down:
Format > Conditional Formating... > Formula is and enter

=COUNTIF(A:Z,"=" & A1)>1 and assign a vivid hi-light format.

Copy the cell and then paste/special format over all cells in cols A thru Z.

Each cell in cols A thru Z will now "light up" if another cell shares its
value