Formatting 0 values to show blank cells
 

I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value.
For printing purposes I need the 0 to not show in the cell (blank cell). I
can do this by using the accounting format, but a dash (-) still shows in the
cell. The sheet is protected to protect the formula. How can I protect AND
not show anything in the cell WHILE keeping the value at "0"?

Formatting 0 values to show blank cells
 

You can use set the custom format to

;;"";

See Worksheet and Excel table basics Formatting numbers in Excel help file
for details.



"RLD" wrote:

I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value.
For printing purposes I need the 0 to not show in the cell (blank cell). I
can do this by using the accounting format, but a dash (-) still shows in the
cell. The sheet is protected to protect the formula. How can I protect AND
not show anything in the cell WHILE keeping the value at "0"?

Formatting 0 values to show blank cells
 

I cannot use ;;""; because it returns no value. I need the value to remain
at 0 because it is part of another SUM formula.

"Hakyab" wrote:

You can use set the custom format to

;;"";

See Worksheet and Excel table basics Formatting numbers in Excel help file
for details.



"RLD" wrote:

I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value.
For printing purposes I need the 0 to not show in the cell (blank cell). I
can do this by using the accounting format, but a dash (-) still shows in the
cell. The sheet is protected to protect the formula. How can I protect AND
not show anything in the cell WHILE keeping the value at "0"?

Formatting 0 values to show blank cells
 

Try something like:
General;-General;;@
(Positive;Negative;Zero;Text)

If you're really using =sum(), you could return "" in your long formula:

=if(isna(vlookup()),"",vlookup())

=Sum() will ignore the text (an empty string).

But if you're doing arithmetic (like =a1+b1), this won't work.





RLD wrote:

I cannot use ;;""; because it returns no value. I need the value to remain
at 0 because it is part of another SUM formula.

"Hakyab" wrote:

You can use set the custom format to

;;"";

See Worksheet and Excel table basics Formatting numbers in Excel help file
for details.



"RLD" wrote:

I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value.
For printing purposes I need the 0 to not show in the cell (blank cell). I
can do this by using the accounting format, but a dash (-) still shows in the
cell. The sheet is protected to protect the formula. How can I protect AND
not show anything in the cell WHILE keeping the value at "0"?

--

Dave Peterson

Formatting 0 values to show blank cells
 

Hi RLD,

Try
Tools
Options
Under the Window Options deselect the Zero value check box.

"RLD" wrote:

I cannot use ;;""; because it returns no value. I need the value to remain
at 0 because it is part of another SUM formula.

"Hakyab" wrote:

You can use set the custom format to

;;"";

See Worksheet and Excel table basics Formatting numbers in Excel help file
for details.



"RLD" wrote:

I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value.
For printing purposes I need the 0 to not show in the cell (blank cell). I
can do this by using the accounting format, but a dash (-) still shows in the
cell. The sheet is protected to protect the formula. How can I protect AND
not show anything in the cell WHILE keeping the value at "0"?