# 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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Alex Blakenburg

##### MrExcel MVP
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. ?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,493
Messages
5,837,673
Members
430,509
Latest member
steve85215

### 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