Amendment to a Data Validation formula

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,046
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try:

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

Forum statistics

Threads
1,211,880
Messages
6,104,547
Members
447,915
Latest member
jpearson

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
Back
Top