Forum Discussion

Copper Contributor

Mar 25, 2022

Solved

Excel IF statement formatting request

I would like to know if you an provide me a IF formula for the restriction of A1 to to other cell - restriction for example A1-1 the next should not put number 1 to the A2, possible as well to put a ...

excel

Formulas and Functions

HansVogelaar
Mar 25, 2022
Luxio97

Do you use comma as decimal separator? If so, the formula should be

=COUNTIF(A:A;A1)=1

for the entire column, or

=COUNTIF(A$3:A$53;A3)=1

for only the range A3:A53

HansVogelaar

MVP

Mar 25, 2022

Luxio97

Do you mean that you do not want to allow duplicates in column A?

If so: select column A. A1 should be the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom in the Allow list.

Enter the following formula

=COUNTIF(A:A,A1)=1

Activate the Error Message tab.

If you wish, enter a message. This will be displayed if the user enters a duplicate value.

If you wanted something else, please explain in more detail.

Luxio97
Copper Contributor
Mar 25, 2022
Perfect. What should I do if I wanted to apply the same criteria for cells A3 to A52?
- HansVogelaar
  MVP
  Mar 25, 2022
  Luxio97
  
  Select A3:A52. A3 should be the active cell in the selection.
  
  Perform the same steps as in my previous reply, but with the formula
  
  =COUNTIF(A$3:A$52,A3)=1
  - Luxio97
    Copper Contributor
    Mar 25, 2022
    HansVogelaar