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"?
0
Utf
3/12/2010 4:47:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
2583 Views

Similar Articles

[PageSpeed] 19

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"?
0
Utf
3/12/2010 5:11:02 PM
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"?
0
Utf
3/12/2010 5:45:01 PM
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
0
Dave
3/12/2010 6:29:07 PM
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"?
0
Utf
3/12/2010 6:36:01 PM
Reply:

Similar Artilces:

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

3.0 Customization
Is it possible in 3.0 to have one set of screens appear for one group of users and another set for another group. For instance, could our service people only see the service screens while our sales people only see the sales screens? I know I can restrict access to different areas, but we want to have a totally different look and feel for each group... Sorry - I dont believe this can be done "Matt Harvey" <rifleman@gmail.com> wrote in message news:OR2vU$3GGHA.740@TK2MSFTNGP12.phx.gbl... > Is it possible in 3.0 to have one set of screens appear for one group of >...

Report CRM 3.0
Hi, I would need to find out the detailed procedure (step by step) to customize a report…. Could anybody inform about any links or documents concerning this issue? Thank's Marco I'm not sure if it's detailed enough, but the technical training manual has a section on creating and customizing reports. You can find it here: http://www.microsoft.com/businesssolutions/crm/using/whatsnewtechnical.mspx HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "Marco Rocca" <Marco Rocca@discussions.microsoft.com> wrote in message news:CEF80683-EC26-456C-82C...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

Send out blank reply
Recently, after receiving a message, my Outlook 2000 is sending out a blank reply message to the sender automatically. Would this cause by setting or virus? ...

Can you create custom activities? MSCRM 3.0
Hi, Is there a way to create a new custom activity instead of customising an existing one? I have created a custom entity called 'Chat' utilising an IFRAME. All works well but this entity really should be an activity considering it's properties. In fact I've just been introduced to MS-CRM 3.0 and don't really understand what the difference is between an entity and activity. Would anyone shed the light for me? BTW, I think 3.0 looks great. Gotta admit it's improved. Cheers. Ty In my experience, you cannot create custom activities. In fact, I have been dire...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

Excel 2002 converts 'S' to 0 when pasting from Clipboard
I came across the following problem: I copied some tabular data from IBM Personal Communications into the clipboard (yes, I am still a user of good old 3270 applications). Then I pasted the data into Microsoft Excel 2002 and all cells containing a 'S' became a '0' (number zero). Next I did some tests and found out that every single uppercase 'S' that is transferred to Excel using copy/paste is translated to '0'. This would not happen with other letters or with words containing an 'S'. Using 'Paste special' I can choose to insert my Clipboard a...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

How do I import from LotusOrg 6.0?Import command only has 5.0
I am trying to import my calendar data from Lotus Org V6.X. Under the file command, it will only import from V5.X. Does anybody have any help for me since I would like to convert to Outlook from Lotus Organizer. Don Kiamie donalbert@mindspring.com In news:32C8F514-3EA5-4802-B1A4-F9C66E77293A@microsoft.com, DonAlbert <DonAlbert@discussions.microsoft.com> typed: > I am trying to import my calendar data from Lotus Org V6.X. Under > the file command, it will only import from V5.X. Does anybody have > any help for me since I would like to convert to Outlook from Lotus > ...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...