Need zero value in Excel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Professor J

no no .

you got it wrong.

in the Data validation under setting you must select custom the source type the formula =NOT(ISBLANK(A1))

and dont forget to uncheck the "ignore blank" as shown in the screenshot.

then it will work.

Conernesto & ProfJJ - I have tried both of your suggestions and neither of them work for me. I am able to delete the contents of the cell thus making it blank.

Conernesto - The only way I am getting your suggestion to work is by replacing the zero with a space; pressing delete does not generate the error message.

Is it just a small range (like A1:A5 in your sample) in the real file? If so a small VBA script could be written to ensure the cells aren't blank.

Professor J

Rob,

from what i understood , the deletion was not part of the question. i believe the questioner asked data validation, if the user want to bypass a cell without filling then the validation error would trigger. however, if author wants to prevent deletion, then it can be done different way.

I have assumed the cells are prefilled with 0 or another value and the question was regarding making sure that the contents of the cell stayed that way, ie couldn't be deleted.

Maybe need clarity from Conernesto as to what is meant by blank and, as I asked earlier, why blank is a problem.

Thanks
Rob H

ASKER

Rob,

Attached is a copy of my worksheet. On Sheet 1, I set up cells A2:A7. These cells have a zero value. If you try to blank/remove the zero value, you will get a message that the celll can't be blank. This is what I wanted and it works. I can close this question if you agree.

Conernesto.
C--For-Experts-Exchange-Data-Validation.

How are you removing the zero? When I press delete I do not get an error message.

If I press Space and enter I do get the message. If that entry were accepted, that is then not blank, it contains a space.

Thanks
Rob

ASKER

You are right. So this works if you enter a space and it can be bypassed if you press delete. This is OK if I can't prevent deleting.

If that's the case, why was blank an issue in the first place?

You can allow for blank or zero in subsequent formulas, do you need assistance with that?

Rob

ASKER

A blank is an issue when you import the information into an Access database. When you create queries you don't get the right answer if there are blank cells.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Yes.

ASKER

The cell format that I used worked to prevent someone from removing a value from a cell. The user can still delete the content but not clear the amount.

The user can still delete the content but not clear the amount.

Can you explain what you mean by this as it doesn't make sense to me. When you delete the content it will clear any value/amount in the cell.

ASKER

I don't think that someone will intentionally delete an amount. On the other hand someone can accidentaly clear an amount and if this happens you will be prompted to enter an amount or zero.