Limit number of times data can be used in a row

[neetal]

Hello all,

I am using Windows XP (soon to be switched to Windows 7), and I'm creating a scheduling form in Excel. I would like to limit the number of times users can put the letter "r" into a row (which defines the dates in the Payperiod. The limit is 8. I tried to come up with a way to use validation, but that didn't work. I'm not certain if this would be a formula or VBA code.

Thank you in advance for your help.

neetal

 

[West Man]

Which cells are to be checked?

 

[neetal]

Here is an example:

Cells B2:AD2, they can use "r" in no more than 8 of those cells

 

[West Man]

The validation formula would be =COUNTIF(B2:AD2,"r")<9

 

[neetal]

Thank you so much for your help with this! I think I might be doing it incorrectly though. I highlighted all the cells for the row, then put in the validation rule as mentioned above, but it didn't stop me from adding more than 8 rs and i didn't get a "Stop" sign though I did add that in. Should I be using validation in a different place?

Again, thank you SO much for your gift of time with this.

 

[West Man]

Change the formula to =COUNTIF($B$2:B2,"r")<9 and enter it as you did. My apologies on the error in the formula

 

[West Man]

The above will check them as they are entered left to right. If they might be entered in some other order use the range $B$2:$AD$2.

 

[neetal]

This one worked PERFECTLY!!! In fact they will be probably entered in random ways, but this definitely put a stop after 8 entries. Thank you again SO much!! You are the best!

 

[West Man]

Happy to have helped Thank you for your expression of appreciation