Amendment to a Data Validation formula

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
I am using a formula in a Data validation as follows

=(E10="YES")*COUNTIF($E$10:$I$10),"YES")<3


This is looking at a row in columns E to I for the word YES and only allowing the word to appear 3 times.

If I want to change this to allow the word training as well, how could I do this please ??

Cheers

Paul
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,451
Office Version
  1. 365
Platform
  1. Windows
I am using a formula in a Data validation as follows

=(E10="YES")*COUNTIF($E$10:$I$10),"YES")<3


This is looking at a row in columns E to I for the word YES and only allowing the word to appear 3 times.

If I want to change this to allow the word training as well, how could I do this please ??

Cheers

Paul
Paul, can you spell it out a bit more?

Do you still require column E to be 'Yes'? or can it be 'training' as well?
Does the 3 times include column E?
Do you mean 'Yes' can occur 3 times AND 'training' can occur 3 times? .. or perhaps 'yes' 3 times OR 'training' 3 times? .. or something else?
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035
Hi Peter

In columns E to I, they can enter either Yes or Training, combined this must be no more then 3

So 3 YES would be fine or 3 TRAINING or 2 YES and 1 TRAINING...or any combination thereof

In order to make this work before, I selected the range E10 to I10 and entered the formula into Data Validation. I hope this makes more sense
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,451
Office Version
  1. 365
Platform
  1. Windows
Hi Peter

In columns E to I, they can enter either Yes or Training, combined this must be no more then 3

So 3 YES would be fine or 3 TRAINING or 2 YES and 1 TRAINING...or any combination thereof

In order to make this work before, I selected the range E10 to I10 and entered the formula into Data Validation. I hope this makes more sense
OK, then select E10:I10 again and try this formula in Data Validation:
=(COUNTIF($E$10:$I$10,"Yes")+COUNTIF($E$10:$I$10,"training"))<=3
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035

ADVERTISEMENT

I tried this Peter, it allows me to OK the Data Validation box without any error messages but when I enter in YES 5 times, it says nothing. Even if I enter in the wrong spelling, like YYY it still allows me ??
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,451
Office Version
  1. 365
Platform
  1. Windows
I tried this Peter, it allows me to OK the Data Validation box without any error messages but when I enter in YES 5 times, it says nothing. Even if I enter in the wrong spelling, like YYY it still allows me ??
1. It won't allow me to enter YES 5 times. Are you sure you used the formula exactly as suggested? .. including the $ signs? .. in Data validation|Custom|Formula ? .. and you applied it to cells E10:I10?

2. It should (as is) allow you to enter wrong spelling. It is only designed to restrict the number of 'yes' and 'training' entries.
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,035

ADVERTISEMENT

Ah I see Peter, I think this is the difference between my original code and the new one. I should have made that point on my original message. That people should only be allowed ...if theyre going to type anything, type YES or TRAINING
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

=AND(OR(E10="Training",E10="YES"),(COUNTIF($E$10:$I$10,"YES")+COUNTIF($E$10:$I$10,"Training"))<=3)
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

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