Data Validation - Prevent Blank Cells

  • Thread starter Thread starter pjyjr
  • Start date Start date
P

pjyjr

I am using the 'Custom' option with the COUNTIF function
in Data Validation to allow on certain entries (strings
starting with I or TT, with fixed length for I) but also
need to make sure an entry is made. Clearing the 'Ignore
Blank' does not seem to work with formulas but works with
lists. Hopefully I am missing the obvious!. Here is the
formula I have: =COUNTIF(B12,"I?????????")+COUNTIF
(B12,"TT*")=1

I tried adding an AND to it: -COUNTIF(B12,"*")

That does not seem to work.

Can I make sure that an entry is made? Or will this
require VBA module outside of Data Validation.

Thanks for your help!
 
Hi
try the following formula in the custom section
=AND(OR(EXACT(LEFT(B12,1),"I"),EXACT(LEFT(B12,2),"TT")),B12<>"")
 
Frank, thanks for the quick response. I tried but
unfortunately it still does not prevent an empty cell.
Could this be due to the fact there is actually validation
in this cell?

Hmmm?

Thanks again. If you have any other idea or suggestion
please let me know. I have noticed all your posts in this
group. Seems you are a big help to a lot of people!

Paul
 
Hi
difficult to prevent an empty cell. You may try the following: Within
the validation dialog uncheck 'ignore blank cells'
 
Did that...only seems to work when I use 'List' and
not 'Custom' to add a function...

Oh well!

Thanks again for your help!

Paul
 
Hi
it only works for a custom formula if you click in the formula bar and
enter nothing
 
Back
Top