Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
I managed to get something working with a Conditional Formatting rule for: =LEN($A1)=0 and a red colored cell highlight. However, this causes empty rows to also have the red background. So when you open the spreadsheet, the first cell of all empty rows is red. Is there a way to have the formatting only on columns for non-empty rows? –  Jen S. Oct 16 '13 at 6:42
    
Added an update to my answer, does this work? –  CLockeWork Oct 16 '13 at 8:36
    
Hi @JenS, would be good to know if this worked out for you :) –  CLockeWork Oct 25 '13 at 8:20
    
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. –  Jen S. Nov 20 '13 at 14:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.