Mandatory cells in a row
I need to ensure ALL mandatory cells in a row are filled in before another row is added. Please see attached file for example.
I've intentionally left blank columns I, O, R and S.
Before I add a second row, I'd like to see an error message identifying the unfilled cells, or even better those unfilled cells highlighted.
If this is not able to be done, any suggestions are most welcome.
EE-Assets-Multi-Road-Template.xlsx
I've intentionally left blank columns I, O, R and S.
Before I add a second row, I'd like to see an error message identifying the unfilled cells, or even better those unfilled cells highlighted.
If this is not able to be done, any suggestions are most welcome.
EE-Assets-Multi-Road-Template.xlsx
HainKurt
how do you add another record?
ASKER
I would add another record just by selecting another road in Column C, i.e. C4 in this example. Typically, this spreadsheet might have hundreds of rows. Or sometimes, just one row. What I'm trying to achieve is consistent data entry. Also I should mention that I've just submitted one worksheet from the actual spreadsheet I'm developing. There are a variety of worksheets for different data entry but each of them needs to have basic fields filled in. (eg Footpaths, Signs, Lights, etc). For this example I've deleted all but the PavementLayers worksheet. Hope this paints a clearer picture!
You might try using the data validation tools, specifying the type of information required and the min/max (e.g., number between 10000 and 99999). Once the cell has been selected, the user cannot exit the cell without entering the required data. Validation rules also allow you to give the user an input message and an error alert. (Don't forget to clear the checkbox for ignoring blanks so it won't do that.)
There's more information about that here: https://support.office.com/en-us/article/Apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249?ui=en-US&rs=en-US&ad=US
Looking at your file, I'd be inclined to use Access for this. Much better at policing garbage in. Required fields just require a Y/N response in the required property.
There's more information about that here: https://support.office.com/en-us/article/Apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249?ui=en-US&rs=en-US&ad=US
Looking at your file, I'd be inclined to use Access for this. Much better at policing garbage in. Required fields just require a Y/N response in the required property.
I added a conditional formatting that may help
* it makes it whole row red if any value is entered but one of mandatory field is empty...
EE-Assets-Multi-Road-Template.xlsx
=AND(OR($C3="",$D3="",$E3="",$F3="",$G3="",$H3="",$I3="",$J3="",$K3="",$L3="",$M3="",$N3="",$O3="",$P3="",$Q3="",$R3="",$S3="",$T3=""),
CONCATENATE($C3,$D3,$E3,$F3,$G3,$H3,$I3,$J3,K3,$L3,$M3,$N3,$O3,$P3,$Q3,$R3,$S3,$T3)<>""
)* it makes it whole row red if any value is entered but one of mandatory field is empty...
EE-Assets-Multi-Road-Template.xlsx
I can easily provide you code that will validate a given row, but the problem is in knowing when to validate. I can tell when a new row is being started, but what if someone goes back and updates a previous row, leaving out a value? Based on that I think that perhaps you should lock the sheet and use a userform to input new rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for such a prompt response. Kurt's works pretty much perfectly, and I can apply the same logic to the other worksheets.