Excel 2003

  • Thread starter Thread starter Robert McPherson
  • Start date Start date
R

Robert McPherson

Looking to return an error message in a column of data if the same value has
been typed in previously
 
Assume that do you want to restrict the duplicate value in A to C Column then
use this formula in Validation.

Place the cursor in A1 cell and press Cntrl+Spacebar which will select the A
Column and hold the shift key and extend the selection up to C Column by
using right arrow (i.e. A to C)

Note that the Active cell should be A1 since the below formula refers the A1
cell. Active cell will have the white background after selection also.

Select Data>>Validation>>Settings>>Allow>>(select) Custom>>Formula (paste
the below formula)

=COUNTIF(A:C,A1)<2

Select Data>>Validation>>Error Alert>>Title type “This No is already pickedâ€
In Error Message>> type “Trying to create Duplicate Record†or your desired
Error Message.

Give ok.

Change the cell reference A:C & A1 to your desired cell, if required

If this post helps, Click Yes!
 
Using data validation you can; but only manual entries/dropdown entries will
be validated

--Select Column B
--Select menu Data>Validation
--Select Custom from the 'Allow' dropdown and enter the below formula
=COUNTIF(B:B,B1)=1

--From the Error alert tab enter the message you want to be displayed
--Click OK and try.

If this post helps click Yes
 
Back
Top