MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Jimmy on December 01, 2000 12:46 PM

I am using this formula

Which works fine, but my question is how can I prevent a user from inputting more than 1 "x" in the cell. If they input 2 "x's" it mess up the total count and then a average formula based on the countif formula. Is thier some way to change this formula to pop up a message box to tell them to input only one "x" or someway to format the cell to prevent more than one "x" being put in.


Posted by Tim Francis-Wright on December 01, 2000 12:53 PM

You have two options:

first, change the formula to be
=COUNTIF(F1:F75,"*x*")--checks for any x in a cell.

second, which does exactly, what you want,
use Data Validation (Excel 97 and later) to
restrict text length in F1:F75 to 1 character
or less: Data/Validation/Allow Text Length/
less than or equal to/1


Posted by Jimmy on December 01, 2000 1:04 PM

Data validation does just what I need, Thanks