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

[Dz1na]

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!

 

[StuLux]

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
	A	B	C	D	E
1	Check Column	Name	Size	File Type	Due Date
2	Valid Entry	Fred	12	XYZ	30/01/2020
3	Missing Values	Bert		ABC	05/08/2020
4	Valid Entry	Harry	14	jkl	06/09/2020
5	Missing Values		18		09/08/2020
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=IF(OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2),ISBLANK(E2)),"Missing Values","Valid Entry")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E5	Cell Value	=""	text	NO
A2:A5	Cell Value	="Missing Values"	text	NO
A2:A5	Cell Value	="Valid Entry"	text	NO

 

[Dz1na]

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?

 

[Dz1na]

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.

 

[Peter_SSs]

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
	A	B	C	D	E
1		m		m	m
2	Check Column	Name	Size	File Type	Due Date
3	Missing	Fred	12	XYZ
4	Valid	Bert		ABC	5/08/2020
5	Valid	Harry	14	jkl	6/09/2020
6	Missing		18		9/08/2020
7
Missing Data

Cell Formulas
Range	Formula
A3:A6	A3	=IF(COUNTIFS(B$1:E$1,"m",Table2[@[Name]:[Due Date]],""),"Missing","Valid")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:E6	Expression	=AND(B3="",B$1="m")	text	NO