Gummyworms1234
New Member
- Joined
- Jul 18, 2019
- Messages
- 28
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?
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?