Building on the theme of Conditional Formatting that was discussed last week, there are several instances where having Repetitive Data (identical data entered more than once) into a database is Undesirable. For instance, if you were an HR Manager, you may wish to have the names of employees entered Only Once in a company database.
It is useful, therefore, to have a Tool to Identify any such repetitive entries. Using Conditional Formatting with a Clever Formula is one way to accomplish this. Noticing that we are using the above Excel graphic as an example, the following COUNTIF formula, (please note the less-than and greater-than characters that together mean Not Equal To in Excel), can do the trick:
=COUNTIF($A$1:$C$5, A1)< >1
Notice too that the first argument refers to the entire database, and uses an Absolute Reference, whereas the second argument refers to the first cell in the database, and uses a Relative Reference.
Since you are using this formula in a Conditional Formatting statement, you want to have a result of TRUE whenever you want the infringing cell to be highlighted. When the formula returns a TRUE, it activates your chosen formatting.
Clever, eh? Give it a try!
Wednesday, April 18, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment