Excel: Data Validation to make a cell required

Question

I have an Excel file with Column Headers in it. I want to give this Excel file to customers and have them fill in a few rows of data.

Several columns are required. but not all are required.

How can I make several columns required in the workbook? The only validation is that the cell is not empty. I don't know in advance how many rows the customer will make. We could show a dialog box/warning, or maybe color the offending cell differently; I really don't care. Also, no VBA or Macro solutions please.

What I've tried (without solution):

1. Searching Google/SuperUser
2. Using Data Validation - Text length greater than zero and do not ignore blanks. Tried this at the cell level and the column level.
3. Using Data Validation - Custom Formula - I can't figure out how to reference the current cell for a "=LEN(currentCell)>0" formula

Thanks in advance

Solution

The simplest method would be to just use Conditional Formatting. Especially if you can use tables:

If you're using a table you can simply select the column and set the conditional format to formula

=[column]2=""

Then format to fill in red.

-The upside of this is that table ranges are dynamic, so as new rows are added the formula will be retained.
-The downside of this is that there's nothing to stop them ignoring the warning.

You could use VBA, but many users will deny macros in their workbooks, disabling the function.

EDIT
A simple way to have conditional formatting show up if a cell is blank in a row that is being used, where you can't guarantee any particular cell will be populated:

=AND($[CurrentColumn]2="",COUNTA($[FirstColumn]2:$[LastColumn]2)<>0)

This formula will activate the conditional formatting if the cell you want highlighted is blank and any other cell in the row is not blank (for those who don't know, a COUNTA formula counts all cells in a range that are not empty). Be sure to exclude any cells containing formulas from the COUNTA as they will be counted as not blank.

Comments

[CLockeWork]

Added an update to my answer, does this work?

[CLockeWork]

Hi @JenS, would be good to know if this worked out for you :)

[Jen S.]

Sorry, I never did get this working and then the requirement was pulled so now I don't have time to come back and try this.