SolvedPrivate

Need zero value in Excel

Posted on 2015-02-05
23
22 Views
Last Modified: 2016-02-11
I need a cell in Excel to have a zero value or a value (I don't want the cell to be blank).
0
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 5
23 Comments
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40591648
=if(a2="",0,a2)
0
 

Author Comment

by:Conernesto
ID: 40591671
Can the celll start out with a zero value for the user? And then, the zero value can be changed but not blanked?
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40591703
Do you want to change data or data validation ?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Conernesto
ID: 40591726
data validation
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40591768
Can the cell start out with a zero value for the user?
When does this have to occur? On opening the Workbook for the first time?

Data Validation can be set such that the cell can not be deleted. Some more details would be good.

Thanks
Rob H
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40591786
Hmm, I thought these Custom DV formulas would work but they don't:

=LEN(E6)<>0
=E6<>""

If it is only certain cells or a range, I suspect you will be able to achieve it with VBA.

Why is blank an issue anyway?
0
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40591979
ok
there are VBA solutions, but i prefer formula in data validation

select the cells that you want to apply the data validation on them.

in the example see the screenshot.   A1:D10   and then in Data validation custom put this formula =NOT(ISBLANK(A1))  then make sure to uncheck the "ignore blanks"

then it will work.

Capture.PNG
0
 

Assisted Solution

by:Conernesto
Conernesto earned 0 total points
ID: 40592096
I tried what you sent, but I am still able to have blank values. What I did that worked is as follows:

1 On cells A1:A5 (my sample area), I changed the cell format to Custom.
2 I entered zeros on A1:A5
3 I created a range file called Values and enter 1000000
4 In the Data Validation screen under settings I set Allow: Whole number, Data: less than or equal to Maximum: = Values. Ignore blank is checked

I then whent to cell A1 and removed the zero and I got a message that a value must be entered.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40592112
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40593380
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.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40593536
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40593598
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
0
 

Author Comment

by:Conernesto
ID: 40593794
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40593876
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
0
 

Author Comment

by:Conernesto
ID: 40593893
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40594033
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
0
 

Author Comment

by:Conernesto
ID: 40594039
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.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40594094
Does using "Is Null" not allow for blanks?
0
 

Author Comment

by:Conernesto
ID: 40594103
Yes.
0
 

Author Closing Comment

by:Conernesto
ID: 40602752
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40602791
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.
0
 

Author Comment

by:Conernesto
ID: 40603294
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40603314
Delete and clear are the same thing, accidentally or otherwise.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question