Link to home
Start Free TrialLog in
Avatar of itsmevic
itsmevicFlag for United States of America

asked on

Excel 2010: Creating Validation for Certain Cells in Certain Columns in Worksheet

Hello Experts -

    I need to create some simple validation checks for a spreadsheet that I am in charge of.  We have several people that use this spreadsheet; the problem with so many hands touching it is that not everyone is consistent when it comes to filling it out like it should be filled out.  For example, some people will leave cells blank or they won't use the drop-downs that contain the pre-filled choices;  Some of these folks will end up using their own variation as a choice.  Not good, especially since everything has to be uniform in this spreadsheet.  I've tried and tried to tell people to keep it consistent, but it's a loosing battle and to be honest i'm exhausted with it.  Any help or suggestion you could provide that would 'FORCE' consistency down the board is greatly appreciated.  Thanks.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you attach a sample workbook and describe one or two of the validations you want performed?
yes you can create validation list for that cell... after that no one can enter anything other than your validation list.. he will get error message.

Thanks
Avatar of itsmevic

ASKER

Martin -

I have a couple of dropdowns within each row.  Some are simple Yes and No responses.   I'd like it so that every question for each cell in each column is answered properly and if it isn't it alerts the user to do so and will not let them proceed unless there is the required value in that field.
At my last employer, the engineers had to use a purchase order form where all the accounting codes had to be 100% correct before the order could be processed. As an experienced user, my odds of getting it right the first time were less than 10%.

One day I got so mad about the PO farrago that I rewrote the workbook so it looked the same, but would be much easier to use. My design goal was that information would only be entered once, and anything that could be looked up would be entered automatically. The user should never need to type in a numeric code, just make selections from commonly understood English words or phrases.

I used data validation dropdowns to restrict the choices, and then did lookups to get the corresponding accounting codes. I used conditional formatting with formula criteria to highlight required fields in yellow--but the yellow highlighting was presented sequentially. As each yellow field was filled in, the next field would be highlighted. Vendor address & phone numbers were taken from the corporate approved vendor list, and choices appeared in a dropdown that would restrict the number of entries displayed based on what you had already typed. I used checkboxes and radio buttons so the user could pick payment method and which type of certs to include with the order. Cell comments explained the choices wherever there might be confusion.

Information that was missing or inconsistent would be highlighted in yellow. When the yellow highlighting was all gone, the form was complete and ready to submit. This feedback of "completeness" was a good feature.

Most of the error-checking and validation was done using worksheet UI tools. The two pages of VBA code in the workbook was used only to download the vendor database info, and to pre-fill the form with your name.

Although I could have done so, I chose not to lock down the form. Like most professionals, engineers are motivated to do their job well and any tool that helps them do that is much appreciated. If someone wanted to break the form, they could do so...but accounting would catch the errors and kick it back for resubmittal.

Bottom line: if it is easier to do the job right than to do it wrong, people will get on board with the new tool.
You could use Conditional Formatting to highlight empty cells and/or use Wingdings font to add a smiley face if the cell has been completed.

You could create a Named Range to represent the answers and VBA could check that range is completed and pop up a message before closing
Smiley.xlsx
Will look into these options
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you.
Did that resolve the problem?
Thanks!
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015
              Experts Exchange MVE 2015
              Experts-Exchange Top Expert Visual Basic Classic 2012 to 2015