SOLVED

Excel IF statement formatting request

Occasional Contributor

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 formula for the whole file and not for single cell only. 

 

Referencen number for the chat 1037151891.

7 Replies

@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.

Perfect. What should I do if I wanted to apply the same criteria for cells A3 to A52?

@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

I tried to use the formula you provided but I cant enter other number or any number, also does it need to start with =, because it is not accepting when it is included.
best response confirmed by Grahmfs13 (Microsoft)
Solution

@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

Perfect, I solved. Thank you very much