# Data validation issues

#### Gummyworms1234

##### New Member
I am using data validation to flag users about changing a number if they attempt to type it twice in a column. I’ll detail what I’m experiencing below.

1. For this issue, I am using column M and column N. In the validation criteria for column M, under allow I am using custom, for data I am using between and for the formula I am using is =(M9>"")+NOT(COUNTIFS(M\$9:\$M\$33,M9)>1). In the validation criteria for column N, everything is the same except for the formula. I am using =(N9>"")+NOT(COUNTIFS(N\$9:\$N\$33,N9)>1) in column N. The thing is when I put 100 for example in column M and 100 in column N, I get an error as if I used 100 twice in column M or N.

2. To get around the first issue I explained above, I used data validation again and used a formula to apply it all the cells in the range I wanted to avoid having to enter everything individually for each column. The formula I used was =(M9>"")+NOT(COUNTIFS(M\$9:\$Y\$33,M9)>1). This formula also made it seem as if I input the same number twice in column M when it was actually the same number in column M and N. Both these issues are occurring randomly and I am not sure why. Sometimes the same numbers in column M and N won’t be an issue and then for other numbers it will be. Can someone explain why this is occurring and help me with a new formula if that is what’s needed?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Alex Blakenburg

##### Well-known Member
Can you elaborate on what the intent was with the (M9>"") part ?
The formula in your item 1 seems to have 1 error in it which might have caused you some of your issues.
You don't want the \$ sign in the 2nd part of the range.
=NOT(COUNTIFS(M\$9:\$M\$33,M9)>1)
• If you were to highlight the range M9:Y33
• Open the data validation box
• Select custom
• in the formula box copy in
=NOT(COUNTIFS(M\$9:M\$33,M9)>1)
• You should find it works for the entire range M9:Y33
If you later want to tweak it.
• Select 1 of the cells
• Go into the same formula box and make your changes but then tick the box in the bottom left corner
"Apply these changes to all other cells with the same settings"

If you want to copy the validation from 1 column to another use: Paste Special clicking the validation radio button

#### Gummyworms1234

##### New Member
Can you elaborate on what the intent was with the (M9>"") part ?
The formula in your item 1 seems to have 1 error in it which might have caused you some of your issues.
You don't want the \$ sign in the 2nd part of the range.
=NOT(COUNTIFS(M\$9:\$M\$33,M9)>1)
• If you were to highlight the range M9:Y33
• Open the data validation box
• Select custom
• in the formula box copy in
=NOT(COUNTIFS(M\$9:M\$33,M9)>1)
• You should find it works for the entire range M9:Y33
If you later want to tweak it.
• Select 1 of the cells
• Go into the same formula box and make your changes but then tick the box in the bottom left corner
"Apply these changes to all other cells with the same settings"
View attachment 36459

If you want to copy the validation from 1 column to another use: Paste Special clicking the validation radio button

View attachment 36458
M9 was the first cell in the range I was selecting. I saw (M9>"") from looking up more examples of data validation and applied it to my situation. With the formula you gave me it works but I wanted to have the error alert appear only when a duplicated value is used. In any column, cells should be able to have the same text multiple times. Could you adjust the formula so error alerts appear only for duplicated values and not for duplicated text?

#### jtakw

##### Well-known Member
Hi,

Not entirely sure, but try this formula for CF:

Excel Formula:
``=OR(ISTEXT(M9),COUNTIF(M\$9:M\$33,M9)<=1)``

#### jtakw

##### Well-known Member

Oops, I meant DV, not CF.

#### Gummyworms1234

##### New Member
Hi,

Not entirely sure, but try this formula for CF:

Excel Formula:
``=OR(ISTEXT(M9),COUNTIF(M\$9:M\$33,M9)<=1)``
This formula seems to do the trick! Thanks for the help!

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Replies
7
Views
146
Replies
1
Views
63
Replies
9
Views
187
Replies
0
Views
23
Replies
5
Views
343

1,129,923
Messages
5,639,024
Members
417,063
Latest member
ShijinMathew

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back