Data validation issues

Gummyworms1234

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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
551
Office Version
  1. 365
Platform
  1. Windows
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"
1618188759424.png


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

1618188572518.png
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Not entirely sure, but try this formula for CF:

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Oops, I meant DV, not CF.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback. 💯
 

Watch MrExcel Video

Forum statistics

Threads
1,129,264
Messages
5,635,153
Members
416,844
Latest member
ryanangus496

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top