Data validation issues

[Gummyworms1234]

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?

 

[Alex Blakenburg]

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]

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]

Hi,

Not entirely sure, but try this formula for CF:

=OR(ISTEXT(M9),COUNTIF(M$9:M$33,M9)<=1)

 

[jtakw]

Oops, I meant DV, not CF.

 

[Gummyworms1234]

Hi,

Not entirely sure, but try this formula for CF:

=OR(ISTEXT(M9),COUNTIF(M$9:M$33,M9)<=1)

This formula seems to do the trick! Thanks for the help!

 

[jtakw]

You're welcome, thanks for the feedback. ?