Excel bells & whistles

December 7, 2005 2:28 PM

Microsoft Excel. Is it possible to insert some "alarm" function that will notify me if I insert duplicate text in later cells?

I am creating an Excel document for data entry. Multiple people will be entering the data from different cases. The first thing we will do is create a unique file name and type it in the first column. Then we will add all kinds of other data in adjoining columns. I would like to insert some feature that will alert the data-entry person if the same file name (first column) is entered later on, so that we don't accidentally enter data for the same case twice. Is this even possible in Excel?

posted by Amizu to computers & internet (4 comments total)

You can do this with the data validation tool.
posted by milkrate at 2:44 PM on December 7, 2005

This is why people use databases. yes, you can probably find a way to make this work, but you're using a hammer on a screw here.
posted by orthogonality at 3:18 PM on December 7, 2005

use the dget() with an if() in a separate column to light up with an "X" or "D" or something if it finds a match to the adjacent cell in the list. use Help to figure out how to use dget(). remember that if you want to specify a cell (like the top of your list to check for matches) and don't want it to increment when you fill down, address it by $A$1 instead of A1.
posted by crabintheocean at 3:54 PM on December 7, 2005

If you use Excel's data form to enter information (it sounds like you're not), data validation doesn't work (I believe).

Data validation pops up an error message. An alternative is to use conditional formating to color cells (say, yellow) where duplicates exist.

The formula would look something like this (select column A, then Format menu, Conditional Formatting):

=COUNTIF(Column_A_values,A1)>1

which means if the value in cell A1 is found anywhere else, formating of the cell (e.g., turning the background to yellow) WOULD occur.

Note 1: Column_A_values is a named range (Insert > Name > Define, set to =A1:A9000, for example.

Note 2: If you select the entire column and then do conditional formatting, Excel is smart enough to use the value A1 for cell A1, A2 for cell A2, etc., rather than A1 in every cell in column A.

Note 3: This approach will result in ALL duplicates being highlighted (or whatever), not the one just entered.

Conditional formatting could also be done in addition to data validation, if you're the kind of person who believes in wearing a belt AND suspenders, for example.
posted by WestCoaster at 12:30 PM on December 8, 2005