• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation to Prevent Blank Cells in a Row

dparteka

dparteka

Member
I'm on a roll here with Data Validation, hopefully with the help I'm getting it'll eventually sink in. I'm looking for a data validation that will prevent blank cells… a more specific explanation is included in the attached spreadsheet... thank you for looking
 

Attachments

Good morning Luke & thanks for the help... I changed the formula a bit:
=IF(COUNTBLANK(B2:D2)+COUNTBLANK(F2:Q2),"-","+")

I also screwed up because I left out an important detail which is why I was looking a data validation. I was thinking that if A2 is "-" (meaning that there is at least one blank cell) this would somehow prevent the date entry in E2 and a validation message would appear. I'm not sure how to go about doing that because E2 already has a validation list... any ideas how I can do that, attached is the spreadsheet.
 

Attachments

I've made a few changes, please disregard the previous spreadsheet, I've attached the newest version here, the text in the previous post still does apply... thank you again
 

Attachments

Okay, I've been busy on this spreadsheet and with your help have figured out a few things on my own and may have hopefully simplified the issue so disregard everything prior.

E2 is my only problem that I can't get past, it is the last entry done on a row. It has a data validation list associated to it which prevents me from creating another validation that would prevent entry if a condition exists. There is more explanation on the attached spreadsheet if anyone may have an idea on how to get past this... thanks for looking.
 

Attachments

If you don't really need a dropdown (which seems a bit long, with so many dates), you could change DV to use a formula like

=AND(COUNTBLANK($A2:$V2)=0, YEAR(E$2)=2018)
 
Excellent, definitely does everything I was looking for and great idea eliminating the date list... have one on me.

One last thing on this and I can put it to bed. The validation works great when typing in the date but is ignored when using an ActiveX trigger, what's up with that? If you get a chance take a look at the attached spreadsheet.
 

Attachments

You must log in or register to reply here.
Back
Top