Mandatory cells? Or highlight blank cells in rows with data?

Dz1na

New Member
Joined
Jan 15, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. MacOS
I'm very new to Excel formulas and really struggling. I'm creating a spreadsheet which lists items and their specifications. Eg. column A has the item name, B has the size, C has the file type, D is the due date etc. I want to make sure that if people add an item that they fill out ALL the specs for that item, not leaving any blanks. (There are a couple of columns that can be blank, like the notes column, but most are required.)

Is there a way to make cells mandatory? Or warn when the user tries to save/close the file with missing information? If not, what's the best alternative? I've seen other people suggest using conditional logic to highlight blank cells, but I'm not sure how to limit that to ONLY the rows that contain an item (since the list may grow or shrink with items being removed or added). Any advice appreciated!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
This is a very quick and simple solution (there are more elegant solutions). This simply holds a formula in Column A that checks there is something in columns B,C,D and E and labels the row as Valid or not. Conditional formatting has been applied to help the user. If the range is defined as a table then any new row added will automatically include the formula in column A

Book1
ABCDE
1Check ColumnNameSizeFile TypeDue Date
2Valid EntryFred12XYZ30/01/2020
3Missing ValuesBertABC05/08/2020
4Valid EntryHarry14jkl06/09/2020
5Missing Values1809/08/2020
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=IF(OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2),ISBLANK(E2)),"Missing Values","Valid Entry")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E5Cell Value=""textNO
A2:A5Cell Value="Missing Values"textNO
A2:A5Cell Value="Valid Entry"textNO
 
Upvote 0
Thanks @StuLux , I've already got the red formatting for the blanks but the validation column is a nice addition. How do I ensure that it will apply to new rows though? I selected the relevant cells and defined it as a table but it didn't look any different, am I missing something?
 
Upvote 0
Never mind, I think I got it! I hadn't properly created the table, but it's fixed now. I've changed the red fill to a red border so it's not so overpowering, but the solution works well I think. :)
 

Attachments

  • Screen Shot 2020-01-16 at 5.04.39 PM.png
    Screen Shot 2020-01-16 at 5.04.39 PM.png
    67.4 KB · Views: 33
Upvote 0
Welcome to the MrExcel board!

Suggesting a possible 'tweak' as you indicated that some cells are not compulsory.
Also, this would be more easily expandable if more columns are added to the table.

Insert a new row above the table and mark the mandatory columns with an "m" as shown. That new row can then be hidden if you want.
Formula in Check Column can now use this new row to check for missing data (
- Note that row 4 shows valid because the only empty cell is in a non-mandatory column
- This formula then only requires a simple change if new columns are added at the right of the table or left of the [name] column and no change required if columns are added between [Name] and [Due Date] columns
Slightly different CF for the main table area. The red borders are not showing up in the XL2BB mini-sheet below but they are the cells showing as white background.

Book1
ABCDE
1mmm
2Check ColumnNameSizeFile TypeDue Date
3MissingFred12XYZ
4ValidBertABC5/08/2020
5ValidHarry14jkl6/09/2020
6Missing189/08/2020
7
Missing Data
Cell Formulas
RangeFormula
A3:A6A3=IF(COUNTIFS(B$1:E$1,"m",Table2[@[Name]:[Due Date]],""),"Missing","Valid")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:E6Expression=AND(B3="",B$1="m")textNO
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top