Data Validation blanks for formulas

[rcl2884]

I am using Excel 2007. This seems silly to me, but I don't see an
easy work around. I have a cell that has data validation set be whole
numbers between 0 and 999 and with "ignore blanks" checked. This
works fine UNLESS I use a formula in the cell that returns a blank, at
which point I get a data validation error.

Note that if I manually enter a blank then I do not get an error.

An example of the formula is

=if(A1="","",A1)

I want to distinguish the value of 0 from the value of blank. Is there
a different way to enter a blank in a formula that will be interpreted
as a blank by the data validation?

Thanks,
Rick

 

[Wouter HM]

Hi Rick,

As far as I know validation works only on values directly enterred
onto a cell.
If you set up validation after enterring your formula there will be no
error nessage.
But when you enter an invalid value into cell A1 the will olso no
error.

May I suggest you use conditional format, which will react on the
result of the formula.

Hoop This Helps,

Wouter

 

[Vacuum Sealed]

Rick

As your Cell in question is set to a Value, it needs to have a value.

So your formula should read =IF(A1="",0,A1), hence the Data Validation
requirement should be satisfied/met.

I'm fairly certain you will find the reference to "Ignore Blanks" refers to
a ( Data Validation List ) whereby it does not display blank cells that
maybe present in your "Named Range" that your list is associated with.

HTH
Mick

 

[Clif McIrvin]

Rick
[ ]

I'm fairly certain you will find the reference to "Ignore Blanks"
refers to a ( Data Validation List ) whereby it does not display blank
cells that maybe present in your "Named Range" that your list is
associated with.

Hmm ... I thought "Ignore Blanks" permitted the user to leave a
validated cell empty, rather than requiring one of the other acceptable
values.

 

[GS]

After serious thinking Clif McIrvin wrote :

Rick
[ ]

I'm fairly certain you will find the reference to "Ignore Blanks" refers to
a ( Data Validation List ) whereby it does not display blank cells that
maybe present in your "Named Range" that your list is associated with.

Hmm ... I thought "Ignore Blanks" permitted the user to leave a validated
cell empty, rather than requiring one of the other acceptable values.

That's correct. If any cells are blank in the 'Named Range' specified
in the DV they will appear as blanks in the cell dropdown.

 

[Gord Dibben]

"Ignore Blanks" checked means users can enter invalid entries when the list
source is a named range.

For more on "ignore blanks" usage see Debra's site.

http://www.contextures.on.ca/xlDataVal08.html#Invalid


Gord Dibben MS Excel MVP

Rick
[ ]

I'm fairly certain you will find the reference to "Ignore Blanks"
refers to a ( Data Validation List ) whereby it does not display blank
cells that maybe present in your "Named Range" that your list is
associated with.

Hmm ... I thought "Ignore Blanks" permitted the user to leave a
validated cell empty, rather than requiring one of the other acceptable
values.

 

[GS]

After serious thinking Gord Dibben wrote :

"Ignore Blanks" checked means users can enter invalid entries when the list
source is a named range.

The behavior I get when "Ignore Blanks" IS checked is that I can leave
the cell empty OR empty the cell if I type an invalid entry, without
getting an alert.

If I uncheck "Ignore Blanks", I get an alert if I try to empty the cell
after making an entry.

If I use a named range with blanks, the blanks appear in the dropdown.

 

[rcl2884]

Thank you all for the replies. "ignore blanks" works as Garry
describes. It allows you to enter a blank without getting a data
validation error, even thought blanks are not on the validation list.

I am not sure Wouter has it quite right. I can enter a formula after
the data validation is turned on, and the data validation works fine
on the formula unless the formula returns a blank. For example, if I
want integers between 0 and 100 and the formula returns a value
between 0 and 100 then I do not get a validation error. If it returns
any value other than 0-100 then I do get an error. This issue is that
I do not want to get a validation error if the formula returns a
blank.

I cannot have the formula return "0" when the value is missing since
missing values are to be treated differently than 0.

Finally, obviously I can place my formulas somewhere else and copy-
past-values back into the right cells, but this is cumbersome. Clearly
there are workarounds, but the issue is that I have a lot of VBA that
interprets blanks in a certain way, and a lot of data validations on
the cells, and when I try to fill the cells with formulas this
produces all the data validation errors.

Since the data validation works fine for formulas in all circumstances
except when they return a blank, I am thinking this is a microsoft
bug... unless there is some alternative way to have a formula return a
blank that gets interpreted by the data validation as a blank, I am
stuck with the inelegant work-arounds.

Thanks again for the help.
....Rick

 

[Clif McIrvin]

What exactly do you mean by "returns a blank" and "tests for blank
cells" ???

The zero length string (ZLS) [ "" ] is NOT a BLANK (ie, empty) cell.


Worksheet function ISBLANK actually tests for an empty (null) cell.
VBA IsEmpty(variant) (AKA IsEmpty(singleCellRange.Value)) also tests for
an empty (null) cell.

I know of no way for a function to return a BLANK (ie, empty) value.

Best suggestions I have for you is to revise your model to include a
visual value [ n/a, ---, or whatever ] that can be included in your
validation list, and returned by your formula.

HTH ... good luck!


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket


Thank you all for the replies. "ignore blanks" works as Garry
describes. It allows you to enter a blank without getting a data
validation error, even thought blanks are not on the validation list.

I am not sure Wouter has it quite right. I can enter a formula after
the data validation is turned on, and the data validation works fine
on the formula unless the formula returns a blank. For example, if I
want integers between 0 and 100 and the formula returns a value
between 0 and 100 then I do not get a validation error. If it returns
any value other than 0-100 then I do get an error. This issue is that
I do not want to get a validation error if the formula returns a
blank.

I cannot have the formula return "0" when the value is missing since
missing values are to be treated differently than 0.

Finally, obviously I can place my formulas somewhere else and copy-
past-values back into the right cells, but this is cumbersome. Clearly
there are workarounds, but the issue is that I have a lot of VBA that
interprets blanks in a certain way, and a lot of data validations on
the cells, and when I try to fill the cells with formulas this
produces all the data validation errors.

Since the data validation works fine for formulas in all circumstances
except when they return a blank, I am thinking this is a microsoft
bug... unless there is some alternative way to have a formula return a
blank that gets interpreted by the data validation as a blank, I am
stuck with the inelegant work-arounds.

Thanks again for the help.
....Rick

 

[rcl2884]

On Jun 3, 4:18 pm, "Clif McIrvin"
"I know of no way for a function to return a BLANK (ie, empty) value."

Well, that's the issue then... That seems funny to me since if I have
one worksheet with a lot of blanks, and I want to reference those
cells in another workbook, then there is no way to reproduce the first
sheet in the second. I have a sheet that gets filled in by non-savvy
users and this sheet has a lot of non-required variables that are
handled by the VBA with default values... so the instructions are, if
you do not have a particular non-required variable, leave it blank.
This is much easier and visually cleaner for the user than entering "N/
A" or "Missing" or whatever in the cells with no values. Sometimes
they get the data supplied in one layout and want to convert it to our
standard format and use formulae to do this. This is where the
problem lies... they cannot do this with the data validation turned
on.

I suspected the issue was that "" was not really a blank.

So, I will refine my question. If you have a formula that references
another cell, and you want to return a blank if the referenced cell is
also blank, is there any way to do this? This seems like this would
be a fundamental requirement.

Thanks again!

What exactly do you mean by "returns a blank" and "tests for blank
cells" ???

The zero length string (ZLS) [ "" ] is NOT a BLANK (ie, empty) cell.

Worksheet function ISBLANK actually tests for an empty (null) cell.
VBA IsEmpty(variant) (AKA IsEmpty(singleCellRange.Value)) also tests for
an empty (null) cell.

I know of no way for a function to return a BLANK (ie, empty) value.

Best suggestions I have for you is to revise your model to include a
visual value [ n/a, ---, or whatever ] that can be included in yourvalidationlist, and returned by your formula.

HTH ... good luck!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket


Thank you all for the replies. "ignoreblanks" works as Garry
describes. It allows you to enter a blank without getting a datavalidationerror, even thoughtblanksare not on thevalidationlist.

I am not sure Wouter has it quite right. I can enter a formula after
the datavalidationis turned on, and the datavalidationworks fine
on the formula unless the formula returns a blank.  For example, if I
want integers between 0 and 100 and the formula returns a value
between 0 and 100 then I do not get avalidationerror.  If it returns
any value other than 0-100 then I do get an error.  This issue is that
I do not want to get avalidationerror if the formula returns a
blank.

I cannot have the formula return "0" when the value is missing since
missing values are to be treated differently than 0.

Finally, obviously I can place my formulas somewhere else and copy-
past-values back into the right cells, but this is cumbersome. Clearly
there are workarounds, but the issue is that I have a lot of VBA that
interpretsblanksin a certain way, and a lot of data validations on
the cells, and when I try to fill the cells with formulas this
produces all the datavalidationerrors.

Since the datavalidationworks fine for formulas in all circumstances
except when they return a blank, I am thinking this is a microsoft
bug... unless there is some alternative way to have a formula return a
blank that gets interpreted by the datavalidationas a blank, I am
stuck with the inelegant work-arounds.

Thanks again for the help.
...Rick

After serious thinking Gord Dibben wrote :

The behavior I get when "IgnoreBlanks" IS checked is that I can leave
the cell empty OR empty the cell if I type an invalid entry, without
getting an alert.

If I uncheck "IgnoreBlanks", I get an alert if I try to empty the
cell
after making an entry.

If I use a named range withblanks, theblanksappear in the dropdown.
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[GS]

rcl2884 wrote :

So, I will refine my question. If you have a formula that references
another cell, and you want to return a blank if the referenced cell is
also blank, is there any way to do this? This seems like this would
be a fundamental requirement.

If A1 is blank, putting the following formula in C1 leaves C1 blank.

=$A$1

 

[Clif McIrvin]

rcl2884 wrote :

If A1 is blank, putting the following formula in C1 leaves C1 blank.

=$A$1

not on my copy of xl2010

c1 returned the value of 0 .. [ zero ]

add these two formulas:

D1: =isblank(a1) [ returns TRUE ]
E1: =isblank(c1) [ returns FALSE ]

I stand by my statement that I know of no way for a formula to return a
BLANK value.

In point of fact, the cell is no longer BLANK, because it now contains a
formula.

 

[Clif McIrvin]

On Jun 3, 4:18 pm, "Clif McIrvin"
"I know of no way for a function to return a BLANK (ie, empty) value."

Well, that's the issue then... That seems funny to me since if I
have one worksheet with a lot of blanks, and I want to reference
those cells in another workbook, then there is no way to reproduce
the first sheet in the second. I have a sheet that gets filled in by
non-savvy users and this sheet has a lot of non-required variables
that are handled by the VBA with default values...

VBA can create an empty [ BLANK ] cell ... but that cell will no longer
contain a formula, either. The simplest way is to use the .ClearContents
method on the cell(s) (Range) in question.

so the
instructions are, if you do not have a particular non-required
variable, leave it blank. This is much easier and visually cleaner
for the user than entering "N/ A" or "Missing" or whatever in the
cells with no values.

Quite sensible.

Sometimes they get the data supplied in one
layout and want to convert it to our standard format and use formulae
to do this. This is where the problem lies... they cannot do this
with the data validation turned on.

I suspected the issue was that "" was not really a blank.

So, I will refine my question. If you have a formula that references
another cell, and you want to return a blank if the referenced cell
is also blank, is there any way to do this? This seems like this
would be a fundamental requirement.

I've never attempted anything quite like what you describe. If your
formula returns a single space, or a forced text space [ "' " ] instead
of [ "" ] the "empty" cells should still "look" empty ... and it might
be possible to construct your formulas so that you get the results you
need.

If that means that sometimes you would have a worksheet with truly BLANK
cells for empty cells, and other times you would have [ ' ]
representing the "empty" cell I'd think you could use the OR function to
test for either case:

= IF (OR (ISBLANK(A1),A1="' "), <A1 is "empty">, <A1 is not "empty">)

Note: "' ", [ ' ] represent <single quote><single space>

HTH!

 

[GS]

Clif McIrvin formulated on Monday :

On Jun 3, 4:18 pm, "Clif McIrvin"
"I know of no way for a function to return a BLANK (ie, empty) value."

Well, that's the issue then... That seems funny to me since if I
have one worksheet with a lot of blanks, and I want to reference
those cells in another workbook, then there is no way to reproduce
the first sheet in the second. I have a sheet that gets filled in by
non-savvy users and this sheet has a lot of non-required variables
that are handled by the VBA with default values...

VBA can create an empty [ BLANK ] cell ... but that cell will no longer
contain a formula, either. The simplest way is to use the .ClearContents
method on the cell(s) (Range) in question.

so the
instructions are, if you do not have a particular non-required
variable, leave it blank. This is much easier and visually cleaner
for the user than entering "N/ A" or "Missing" or whatever in the
cells with no values.

Quite sensible.

Sometimes they get the data supplied in one
layout and want to convert it to our standard format and use formulae
to do this. This is where the problem lies... they cannot do this
with the data validation turned on.

I suspected the issue was that "" was not really a blank.

So, I will refine my question. If you have a formula that references
another cell, and you want to return a blank if the referenced cell
is also blank, is there any way to do this? This seems like this
would be a fundamental requirement.

I've never attempted anything quite like what you describe. If your formula
returns a single space, or a forced text space [ "' " ] instead of [ "" ] the
"empty" cells should still "look" empty ... and it might be possible to
construct your formulas so that you get the results you need.

If that means that sometimes you would have a worksheet with truly BLANK
cells for empty cells, and other times you would have [ ' ] representing the
"empty" cell I'd think you could use the OR function to test for either case:

= IF (OR (ISBLANK(A1),A1="' "), <A1 is "empty">, <A1 is not "empty">)

Note: "' ", [ ' ] represent <single quote><single space>

HTH!

And so does this not mean that the cell containing the function is no
longer blank? Quite the dilemna!

 

[GS]

Clif McIrvin wrote on 6/6/2011 :

rcl2884 wrote :

If A1 is blank, putting the following formula in C1 leaves C1 blank.

=$A$1

not on my copy of xl2010

c1 returned the value of 0 .. [ zero ]

add these two formulas:

D1: =isblank(a1) [ returns TRUE ]
E1: =isblank(c1) [ returns FALSE ]

I stand by my statement that I know of no way for a formula to return a BLANK
value.

In point of fact, the cell is no longer BLANK, because it now contains a
formula.

Yes, I believe you are correct when the cell containing the formula
returns its result. Duh.., thanks for the wakeup, Clif!

 

[Clif McIrvin]

Yes, I believe you are correct when the cell containing the formula
returns its result. Duh.., thanks for the wakeup, Clif!

no prob.

Rock On! NGs <g>

 

[rcl2884]

"In point of fact, the cell is no longer BLANK, because it now
contains a
formula."

Thank you all. I think Clif's statement above captures the dilemma.
The validation does not recognize the cell as blank since it has a
formula, even if the resolution of the formula looks blank.

If the user is going to use formulas to transfer and translate the
values from one worksheet into our standard template, then the
validation has to be turned off. I can do the validation in the VBA
that uses data, giving the user warning messages if they enter
incorrect values, but this is tedious for me and cumbersome for the
user since they do not get immediate feedback when they enter an
invalid entry.

But the data validation is problematic anyways since if you copy-paste
invalid values from another source, it does not trigger the validation
message anyways. This means that I probably should not rely on the
data validation even without the "blank" issue.

Thanks again,
Rick

 

[Clif McIrvin]

"But the data validation is problematic anyways since if you copy-paste
invalid values from another source, it does not trigger the validation
message anyways. This means that I probably should not rely on the
data validation even without the "blank" issue."

Rick -- you can use the SheetChange event to do validation ... that
would trigger even for pasted data ... also for multi-cell ranges.

This bit of air code might give you some ideas ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
With c.Validation
If Not .Value Then
MsgBox .ErrorMessage, vbCritical, _
.ErrorTitle
End If
End With
Next c
End Sub

Consult the on-board help for more information.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket


"In point of fact, the cell is no longer BLANK, because it now
contains a
formula."

Thank you all. I think Clif's statement above captures the dilemma.
The validation does not recognize the cell as blank since it has a
formula, even if the resolution of the formula looks blank.

If the user is going to use formulas to transfer and translate the
values from one worksheet into our standard template, then the
validation has to be turned off. I can do the validation in the VBA
that uses data, giving the user warning messages if they enter
incorrect values, but this is tedious for me and cumbersome for the
user since they do not get immediate feedback when they enter an
invalid entry.

But the data validation is problematic anyways since if you copy-paste
invalid values from another source, it does not trigger the validation
message anyways. This means that I probably should not rely on the
data validation even without the "blank" issue.

Thanks again,
Rick

 

[GS]

In addition to Clif's comments, you could also implements an events.cls
that monitors all workbook activity without needing code behind sheets
or ThisWorkbook in their respective event handlers because the
events.cls would accomodate all of that for you. This will allow you to
use Validation either via DV and/or VBA.