If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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"? |
#2
|
|||
|
|||
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"? |
#3
|
|||
|
|||
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"? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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"? |
Thread Tools | |
Display Modes | |
|
|