Excel - Making fields required when data is input in a certain cell

So say we have a column called "base salary" - when a customer inputs this data into the field we want there to be a requirement for other fields to be filled out in that same row that include:

o   Incumbents (Number 1 or greater)
o   Match [Choose one from (Equal,High,Low)] (drop down menu)
o   FLSA Status [Choose one from (Exempt,Nonexempt,Unknown)] (drop down menu)
o   Paid OT [Choose one from (Yes,No)](drop down menu)
o   Base Salary(Number)    
o   Commission (Number)
o   Bonus/Profit Sharing Amount (Number)

Can this be done through a VBA Macro? I've seen some various code online but nothing that does what I need it to do above, and i'm just not strong in the coding department.
nflynn85Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
A sample workbook would be very helpful.
nflynn85Author Commented:
See attached
test-sheet.xlsx
Roy CoxGroup Finance ManagerCommented:
Without using VBA you could create a Conditional Format that highlights the required cells if a base salary is emtered.

With VBA one way would be to have a UserForm pop up requiring the user to complete the entries and save them from the userform to the sheet. Let me know if this might be asuitable and I'll create a sample userform
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

nflynn85Author Commented:
We'd like it to be that so once they enter the data into the "base salary" cell/field, they HAVE to enter the data in the rest of the required fields. Not letting them continue on until they are filled.

Would the conditional format still allow that? Or is that best done through VBA? If it could be done without anything popping up, that would be great.
Roy CoxGroup Finance ManagerCommented:
Conditional formatting would not force the user to enter values, just remind them.

I thought a UserForm because it is a simple way to enter data and if the user closes it without entering data then the base salary entered could be deleted. They would have to re-enter it and the form would open again.

You could lock the relevant cells and once a base salary is entered just unlock the next cell and move the user to that cell, then repeat. This way would require more coding is probably clumsier.

Which column contains the base salary?
Martin LissOlder than dirtCommented:
I added Data Validation for the required columns. If any data is entered into the current year sheet and any required data on that row is missing the workbook can't be closed.
28712488.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook with a Sheet Change Event Code on ThisWorkbook Module.

See if this is something you can work with.
test-sheet.xlsm
[ fanpages ]IT Services ConsultantCommented:
As Martin has kindly added (see ID: 40969457, above), you can add "Data Validation" to any of the cells within any of the columns associated with the rows of your worksheet to prompt the user to complete the data entry to your requirements (as summarised above):

[ https://support.office.com/en-GB/article/Apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249 ]

All this can be achieved without any "coding" expertise.


"We'd like it to be that so once they enter the data into the "base salary" cell/field, they HAVE to enter the data in the rest of the required fields. Not letting them continue on until they are filled."

However, do you wish to enforce data being present in any/all of the columns you mentioned before a user can advance to the next row of data, or before the workbook can be saved?  Enforcing completion of a single row may not be possible in some circumstances, & you would, therefore, effectively make a user lose/clear data in order to progress.

That requirement, or a variation of it, is going to need some form of Visual Basic for Applications [VBA] code to accomplish.

Yes, you can apply Conditional Formatting (also mentioned above), but that simply colo(u)rs/patterns/highlights cell contents rather than insisting a specific data entry takes place (or any data is present in the pre-defined cells of the same row).

"Forcing" completion of a single row (so that all cells you mentioned contain valid data) can be problematic for users if they wish to "pick'n'choose" where & when data is added for specific rows.  You are making data entry very linear, rather than the user-defined (read: random) approach that some of your users may be more accustomed to applying to a worksheet.

A better (more user-friendly) approach to data entry may be to not validate any row until the user wishes to save or close the workbook.  Adopting that method means that all the data is tested for compliance/consistency at once, rather than on a row-by-row basis as soon as a single cell on any row is changed.  An unsuccessful entry of the entire contents of the worksheet would be flagged together, & changes could then be made to rectify the complete set of data at the end of the entry process.

Some users may prefer that, rather than being forced to always complete a single row before another row of data can be started to be entered.
nflynn85Author Commented:
The amount of data entered will vary from customer to customer depending on the roles they have at their place of business. At best, we would only expect a partial workbook to be completed and not the entire thing.

If they are entering a "base salary" number, we need the rest of the fields marked as required to also be filled out. We need this because last year we had some customers only fill out the base salary field, and nothing else.

This made their submission useless to us, and we'd have to chase after the customer to get the rest of the data filled out.

I think the data validation is going to work. Let me check with my team and i'll get back to you all.

Thanks for all the help thus far this has been great
nflynn85Author Commented:
I added Data Validation for the required columns. If any data is entered into the current year sheet and any required data on that row is missing the workbook can't be closed.

One of the things I’m noticing is that I’m unable to delete or change my responses If I’ve entered a value in one of the required cells.  I have to use the undo button to clear a response to start over.  This will frustrate end users if they can’t edit as they move from cell to cell; example: what if they input a value in the wrong row.  I wouldn’t want them to have to undo all their work

is there a workaround for this?
Martin LissOlder than dirtCommented:
I'm sorry but I don't understand. If they have entered several values properly and then put one in the wrong row, pressing the undo button will undo the last one entered, not all their work.
nflynn85Author Commented:
For example say I enter information for the wrong job title.  With the drop down boxes, if I try to clear a cell (delete button) because I selected an option by accident, I’m unable to clear the result.  And the prompt shows, but I’m unable to alter the form after I’ve selected the option from the drop down menu (required cells); if forces me to select one of the options.  The only thing that works is to left click and select “clear contents.”  However, this is not intuitive for our users.  They will also need the ability to use the delete button.

In the original form, I’m able to clear my result after I’ve selected an option from the drop down menu.  Users need the ability to make edits, since it’s a large spreadsheet; they’re bound to make a mistake when entering information and need the ability to correct entries within the form.
Martin LissOlder than dirtCommented:
I can't reproduce your problem because the workbook you posted and I updated does not have the "Title Definitions" sheet that is the source for the "Job Title" column ad I also don't see a "delete button" unless you mean the delete key on the keyboard. Can you post your actual workbook?
nflynn85Author Commented:
You don't need the entire workbook to reproduce the issue; posting the whole thing would be  a security/intellectual property violation. The only place where the forced values apply are on this page.

We gave you the sheet initially to work with in the top of this post. That sheet is the original form, and I can clear my results after I select an option from the drop down menu with the delete key on the keyboard (without the conditional formatting).

With the sheet you provided, lets say I select something incorrectly like the wrong value for that particular job. I want to change it, but i'm forced to use one of the options the prompt displays. I can only click and do a clear contents, or use the undo button. I cannot use the delete button on the key board to clear the cell.

Is there a workaround for this or nah?
Martin LissOlder than dirtCommented:
Sorry if I'm being dense but I'm not following you. Would it be possible for you to post a video where you reproduce the problem?
nflynn85Author Commented:
I appreciate the help you gave. Thank you
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.