Data Validation - Allow "" in Number Cell

[mlv]

It's amazing how much time can be wasted trying variations of a custom
formula in Data Validation without hitting on the right syntax :-(

I have a cell (assume A1) formatted as a number to 2 decimal places.

Normally a formula will enter the appropriate number in the cell, otherwise
the formula will enter "".

Sometimes the formula will be overwritten and a number will be entered
manually. Because the number may be entered manually, I want to set a Data
Validation check. Data Validation will need to allow both "" or any number
(including 0.00).

I was trying to use the ISNUMBER function in Data Validation custom -
something like:

=AND(A1<>"",NOT(ISNUMBER(A1)))

but it doesn't seem to work.

Can someone point me in the right direction?

TIA

 

[GerryGerry]

just use a custom data validation but check (tick) the ignore blank box

The custom validation should be
=ISNUMBER(A1)

Regards
Gerry

 

[mlv]

OK, I think I've got there.

The Data Validation custom formula seems to work with:

=OR(A1="",ISNUMBER(A1))

Guess I originally had things AAF.

 

[mlv]

just use a custom data validation but check (tick) the
ignore blank box.

Hi Gerry

I don't think that will work.

AFAIK, Data Validation does not see "" as a blank, and consequently the
function fails when the formula tries to enter "".

I did ask in a previous post whether a formula can be made to enter a true
blank, rather than "", but I never got a response.

 

[Dave Peterson]

You can make the cell appear empty by returning a zero length string (=""). But
the cell can't be empty if it contains a formula.

I don't understand how you're going to use data|Validation and a formula in that
cell at the same time.

 

[GerryGerry]

You can type the formula in the cell once the data validation is in place.
It will only accept the formula if the formula result passes the validation
criteria.

 

[Dave Peterson]

And when the formula recalculates and doesn't meet the data|validation rules,
what happens?

I still don't get it.

 

[mlv]

And when the formula recalculates and doesn't meet the
data|validation rules, what happens?

Then the Data Validation error message box is displayed and you're asked to
correct the cell entry. This might mean modifying the formula to give the
correct result, or modifying the Data Validation rules to accept what the
formula is returning.


It seems that the cell formula and the associated data validation can be
entered in either order.

When data validation is initially set, it ignores anything that is already
in the cell, including formulas.

The data validation checks the cell content at the next
refresh/recalculation and throws up an error message if the cell content
conflicts with the validation criteria. If the cell content is a formula
that returns a valid result (as allowed by the data validation settings),
then there is no conflict and the result is displayed.

 

[Harlan Grove]

Then the Data Validation error message box is displayed and you're
asked to correct the cell entry. . . .

Wrong!

Data validation is ONLY triggered on cell ENTRY, not on recalculation
of a cell's value. Simple test: give cell A1 the validation formula
=AND(A1>=0,A1<=10). Enter 5 in A1, no problem. Enter 500 in A1, it
doesn't accept the entry. Enter =B1 in A1, no problem because empty B1
would be evaluated as numeric 0. Now change B1 to 1000. What happens?
Don't guess this time, try it!

 

[Dave Peterson]

And to add to Harlan's response...

If you really want to use something that checks for valid entries as the result
of formulas, you can either use an adjacent cell (format the error message in a
big, bold, red font).

Or you could use a worksheet event -- worksheet_calculation if the cell contains
a formula or worksheet_change if the cell is changed by typing/pasting.

If macros are disabled (or events disabled), then the event suggestion won't
work.

If calculation is set to manual, you may not like the adjacent cell.

But I don't see how data|validation fits into your requirements.

 

[mlv]

Harlan Grove chastised:

Wrong!
Data validation is ONLY triggered on cell ENTRY, not on
recalculation of a cell's value.

Hmm, I think I see what you mean. To get the formula accepted at inception,
it has to return a value that is accepted by the Data Validation criteria
(if Data Validation is set). After that, it doesn't seem to matter and any
value is accepted and displayed in the cell.

I had assumed that recalculation of a cell value by a formula would be the
equivalent of a new cell entry. Why wouldn't it be?

Don't guess this time, try it!

I didn't guess last time. I was testing by reinitialising the formula, so
Data Validation was triggered every time and I got the results I reported.
Because I was using reinitialisation of the formula, I didn't pick up on the
anomaly that a change of value in a cell is not validation checked when it
is the result of a formula recalculation.

Why, when a formula recalculates and changes a cell's value, isn't it
considered to be a new cell entry, just as if someone had manually entered a
new value? It seems logical (to me) that it should be. Perhaps I'm missing
something here?

Now I've got to rethink my approach. Guess I have to incorporate any
necessary data validation into the formula instead.

 

[mlv]

But I don't see how data|validation fits into your requirements.

Basically it was a consequence of having to allow users of a particular
Worksheet to overwrite a formula under certain circumstances and to directly
enter a value into the cell themselves. Data Validation was set to ensure
the user manually entered valid data.

I have actually got the formula and Data Validation working together just
fine now. The problem was persuading Data Validation to allow the formula
to initialise when it was trying to enter "" in the cell. Data Validation
doesn't seem to recognise "" as a blank, so ticking 'Ignore Blank' on the
Data Validation dialogue box didn't work.

However, I'm going to modify the Worksheet concerned (and the formula), so
that the user can enter the override data into a separate cell outside of
the print area, from where the formula can pick it up. That way Data
Validation is not required for the cell with the formula, but can be set for
the override cell instead.

 

[Dave Peterson]

When I want to give the user the option of using a result of a formula or
override that formula, I'll use 3 cells.

Say A1, B1, and C1
A1 would contain the formula (and lock the cell and protect the sheet)
B1 would be for the override
C1 would be a formula that determines which cell should be used (also locked)

=if(b1="",a1,b1)

 

[mlv]

When I want to give the user the option of using a result
of a formula or override that formula, I'll use 3 cells.

Say A1, B1, and C1
A1 would contain the formula (and lock the cell and protect the sheet)
B1 would be for the override
C1 would be a formula that determines which cell should be used (also
locked)

=if(b1="",a1,b1)

Thanks Dave. That's basically what I intend to do, but only using 2 cells.

Assume A1 & R1.

A1 is part of the printed Worksheet.
R1 is outside of the printed area.

A1 contains the formula (cell locked and protected)
R1 is for the manual override (formatted as appropriate and Data Validation
set)

Formula (in A1) would be:

=IF(R1="",<formula-do whatever>,R1)

Seems to work. Is there an advantage to the 3 cell scheme?

 

[Dave Peterson]

Just that you can see what each option provides.

Thanks Dave. That's basically what I intend to do, but only using 2 cells.

Assume A1 & R1.

A1 is part of the printed Worksheet.
R1 is outside of the printed area.

A1 contains the formula (cell locked and protected)
R1 is for the manual override (formatted as appropriate and Data Validation
set)

Formula (in A1) would be:

=IF(R1="",<formula-do whatever>,R1)

Seems to work. Is there an advantage to the 3 cell scheme?

 

[Harlan Grove]

I had assumed that recalculation of a cell value by a formula would
be the equivalent of a new cell entry. Why wouldn't it be?

....

Just because it isn't.

Excel distinguishes between cell entry and cell evaluation. That's
just the way it's designed. Besides, data validation is a toy feature.
It's easily bypassed by pasting plain text into cells. For example, in
an new workbook give cell A1 the data validation formula =A1>=0. Now
copy the following nonblank line from this newsgroup posting

-100

switch back to Excel, move to this particular cell and press [Ctrl]+V.