Data Validation - ignore blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up a data form and have data validation rules on the various input
cells. I see that there is an option called "Ignore blanks" in the data
validation settings, which is supposed to restrict someone from entering past
an input cell without entering valid data, if the "Ignore blanks" box is
unchecked. I have tried to do this, but it does not seem to work. Please
could someone tell me what I am doing wrong?
 
Provide information, or better yet, a screen capture of the Data Validation
dialog box with the Setting tab selected (User ALT+PrintScreen to copy the
image to the Windows Clipboard, then paste into your post reply and indicate
what range was selected when you defined the Data Validation..
I need to see how you defined the settings then I might be able to tell you
where you are missing the boat.
TTFN
JMMach, MOUS Master
 
Hi JMMach,

I have tried to paste the screenshot, but the Paste option is greyed out
when I try to paste it here.
 
How about attaching Excel file, or if it has sensitive information then
paste the dialog box into Word or WordPad and attach that to the message.
TTFN
JMMach
 
How does one attach a file to a posting?

JMMach said:
How about attaching Excel file, or if it has sensitive information then
paste the dialog box into Word or WordPad and attach that to the message.
TTFN
JMMach
Click to expand...
 
Unchecking 'Ignore blanks' doesn't force users to enter a value in a cell.

No matter what data validation settings you've applied, users will be
able to leave the cell blank, or select the cell, and press the Delete
key, to clear the cell.

However, if the cursor is in the cell, or in the formula bar, they won't
be able to press the Delete key, to clear the cell, or press the Enter
key while the cell is blank.

You could use programming to check for blank cells, or use conditional
formatting in conjunction with the data validation. For example, turn
the other cells in the row to black fill if the cell with data
validation is blank.

If the cell with data validation in cell A2, select cells B2:H2
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =$A2=""
Click the Format button, and on the Pattern tab, select the black colour
Click OK, click OK
 
In the reply to groups window, you will notice an icon with a paper clip,
click on it and locate the document to be attached.
If that does not work for you then simply type the details of the Settings
tab.
TTFN
JMMach
 
Please don't post files in a non binary newsgroup!

--
Regards,

Peo Sjoblom

(No private emails please)
 
Debra said:
Unchecking 'Ignore blanks' doesn't force users to enter a value in
cell.So what's the purpose of that check box :confused:
Click to expand...


Debra said:
... ...to check for blank cells, or use conditional
formatting in conjunction with the data validation.
Click to expand...
Conditional formatting is no solution since you can't enter a "IsBlank
or equivalent criteria, only "Cell value Is" or "Formula Is". Do yo
have more ;
 
Select the cells that you want to format, e.g. F1:F20
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell:
=F1=""
(you can see the name of the active cell in the Name box, at the left
of the formula bar)
Click the Format button, and choose a dark fill colour on the Patterns tab.
Click OK, click OK
 
And to colour cells based on the content of another cell in the same
row, use that cell in the formula, e.g. when formatting cells C1:J1,
check the contents of cell A1: =$A1=""
 
Back
Top