Show zero values in specific area

I know it is possible to show zero values in a sheet by ticking "zero 
values" in options, but I have a situation where zero values should only be 
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values entered, 
but it plays havoc with the data validation (Decimal greater than or equal 
to 0).

Is there any way I can retain operation of the data validation, but still 
display zero values in a specific range?

--
Ian
-- 


0
IanC
4/20/2010 11:11:15 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
886 Views

Similar Articles

[PageSpeed] 29

The only way I can think of is to use the option "show zero values"; then in 
the area where zeros are not to be shown forma the cell with something like 
#;#,"" to hide zeros
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"IanC" <me@me.com> wrote in message 
news:Nyjzn.327353$0t.180570@newsfe17.ams2...
> I know it is possible to show zero values in a sheet by ticking "zero 
> values" in options, but I have a situation where zero values should only 
> be shown in certain areas of the sheet.
>
> I've tried setting formatting to text and this displays zero values 
> entered, but it plays havoc with the data validation (Decimal greater than 
> or equal to 0).
>
> Is there any way I can retain operation of the data validation, but still 
> display zero values in a specific range?
>
> --
> Ian
> -- 
>
> 
0
Bernard
4/20/2010 3:46:19 PM
Use custom number formatting, to which there are 4 components, each separated 
by a semi-colon

[positive];[negative];[zero];text

You get there by selecting the cells you want to format and pressing Ctrl-1

To show only positive and negative, use something like

#,##0;-#,##0;

To show positive, negative, and zeros, use something like

#,##0;-#,##0;0     or #,##0;-#,##0 [notice there's no trailing semi-colon]



"IanC" wrote:

> I know it is possible to show zero values in a sheet by ticking "zero 
> values" in options, but I have a situation where zero values should only be 
> shown in certain areas of the sheet.
> 
> I've tried setting formatting to text and this displays zero values entered, 
> but it plays havoc with the data validation (Decimal greater than or equal 
> to 0).
> 
> Is there any way I can retain operation of the data validation, but still 
> display zero values in a specific range?
> 
> --
> Ian
> -- 
> 
> 
> .
> 
0
Utf
4/20/2010 4:05:01 PM
Another possibility is to leave all zeros shown and use Accounting format for 
all of the areas where you don't want the zero digit displayed.  With this 
format zero displays as -.  this lets you know something is being calculated 
there, but does not fill up a sheet with 0 digits.

You can also create you own custom format and use - for zero, but the 
Accounting format is standard and easy.
-- 
If this helps, please remember to click yes.


"IanC" wrote:

> I know it is possible to show zero values in a sheet by ticking "zero 
> values" in options, but I have a situation where zero values should only be 
> shown in certain areas of the sheet.
> 
> I've tried setting formatting to text and this displays zero values entered, 
> but it plays havoc with the data validation (Decimal greater than or equal 
> to 0).
> 
> Is there any way I can retain operation of the data validation, but still 
> display zero values in a specific range?
> 
> --
> Ian
> -- 
> 
> 
> .
> 
0
Utf
4/20/2010 5:16:01 PM
Thanks to Bernard, Duke and Paul. I didn't realise there could be so many 
options for something this simple! Thanks for your suggestions.

Paul - I've discounted your method as it shows - even if nothing has been 
entered.
Duke & Bernard - I've used a variation of your methods but have a question.
In some cases the entries may be integers or 1dp so I've gone for #.#;-#.#;0
The problem with this is that when a decimal point is still shown when an 
integer is entered (eg 1. instead of 1). Whilst it's not a big problem, 
aesthetically I'd prefer to lose the point.

Any ideas?

--
Ian
--

"IanC" <me@me.com> wrote in message 
news:Nyjzn.327353$0t.180570@newsfe17.ams2...
>I know it is possible to show zero values in a sheet by ticking "zero 
>values" in options, but I have a situation where zero values should only be 
>shown in certain areas of the sheet.
>
> I've tried setting formatting to text and this displays zero values 
> entered, but it plays havoc with the data validation (Decimal greater than 
> or equal to 0).
>
> Is there any way I can retain operation of the data validation, but still 
> display zero values in a specific range?
>
> --
> Ian
> -- 
>
> 


0
IanC
5/14/2010 5:17:11 AM
Reply:

Similar Artilces:

Display all cells values of that row from where a cell value retri
Hi All, I'm a baby to Excel Programming. But I've to programme Excel 2003 work book in order that if sth is typed in the text box control. The code searches all the worksheets and displays the value in the label control. I've a textbox control, label control and a command button control in Sheet 1 Now with the below mentioned codes I suceeded to retrieve a cell value in a lable control. But the problem is, I've no idea to display all the cells value of that from where the cell in a row, a data was retrieved. Any Idea!! Please Help!! SOS Please consider!...

How to vlookup and summing value?
Hi, please help.... Worksheet name: "Pages" Column A Column B James $100 James $50 AJ $30 Light $46 James $80 When i type on cell A1 on a separate worksheet this formula =VLOOKUP(A1,Pages!A2:B5,2,FALSE) , it only provide me $100. Is there a way to sum up the total to be $230 under "James"? Hi, =SUMPRODUCT((A1:A15="James")*(B1:B15)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the ...

Convert String to Numeric Values
I have data string that I must convert to numeric values. Ex: 00100200300400500600700800901000110015016 How do I get the above data string example to return the numeric values that are three digit integers. How do I get the number 16 for example? Any assistance is appreciated -- JenISCM Try this: Public Sub MySplit(txtIn As String) ' copy/paste to a standard module ' input from debug window: ' call mysplit("00100200300400500600700800901000110015016") Dim i As Integer Dim n As Integer i = 1 For n = 1 To CInt(Len(txtIn) / 3) Debug.Print n & " - " ...

getting value from a cell into another cell behind a text string
Hi, I'd like to know how to get a value from a cell into another behind a text string by using a formula. For example, A B 1 20 Result (20) Thanks, Xianbo Use formatting Pull-down Format > Cells... > Number > Custom and the the field enter: "Result ("General")" The advantage of using formatting is that the cell can still be used for calculations -- Gary''s Student "Xianbo" wrote: > Hi, > > I'd like to know how to get a value from a cell into another behind a text > string by using a formula. Fo...

Excel chart data labels showing % change instead of value?
I'm needing to have my excel chart show me the percent change in my 2 series. I would like this in a data label over series 2 on the chart, but all I can figure out to do is have the value labeled. Does any one have any suggestions? I'm believe I'm using Excel 2002. You can use Rob Bovey's XY Chart Labeler. http://www.appspro.com/Utilities/ChartLabeler.htm "MFritz" <MFritz@discussions.microsoft.com> wrote in message news:29317BE2-4574-4623-8D5E-A3BA97B3FAE6@microsoft.com... > I'm needing to have my excel chart show me the percent change in my 2...

I want cells to show green if <10% and red if>10%. please help
I am trying to make cells show a green background if the result is less than 10 and a red background if the result is grater than 10 I am relatively new to Excel and although I have spent some time trying I cant work this one out. Pleas Help! First select and highlight the cell(s) you want to use. Format > Conditional Formatting > Select "Cell value is" "Less than or equal to" ".1" (place decimal point one in the selection) Now click the "Format" button and select the "Pattern" tab and select your shade of green. Now click the &...

display order of values without sorting
I would like to create a sheet that can give me a weighted score at the end of the row. What I mean by this is I have a sheet with 4 colums and 10 rows, each of the rows will have a weighted number in each of the colums with a total at the end. With out sorting the rows I would like to rank them in importance, the highest number being a 1 the next a 2 the next highest a 3 and so on. Is there a function that does this or does someone have a formula? Thanks Try this… This formula will create logic and will assign a value for the equivalent word. =IF(A1="two"...

I need a shortcut to make a excel file open to a specific sheet
I need to know how to modify an excel shortcut to make a file always open to a specific named "intro" sheet. You can use a macro: http://vbaexpress.com/kb/getarticle.php?kb_id=338 ************ Anne Troy VBA Project Manager www.OfficeArticles.com "EAHRENS" <EAHRENS@discussions.microsoft.com> wrote in message news:4CEF8135-5F57-400F-B5AF-9A11396110A8@microsoft.com... >I need to know how to modify an excel shortcut to make a file always open >to > a specific named "intro" sheet. Try doing it within the files workbook_open event in the ThisWork...

Showing database properties in diagram
Hi people I have scanned the relevant groups and haven't seen an answer to my simple problem. Can I change which of the database properties are shown on the entity diagram? Specifically, can I configure it to display the 'notes property? I am trying to use Visio as an heuristic for building an Access database. I therefore want to start with entity diagrams with little detail and iteratively raise the detail and accuracy of the definitions. Some tables are fully defined and have 30+ columns. Having to show all these does not help my objective and does take up a lot of room. Con...

Returning a Value from Another Worksheet
I have two worksheets in one Excel 2007 file that I'm working with, in which I want the second worksheet to return values of the first worksheet. Hope I can explain it clearly here: The first sheet contains a list of data and numbers to caculate a points system. For example: Worksheet1 Name Column B Column C Points Data1 100 5 4 Data2 75 2 1 ... 2) In the second worksheet, I want to be able to input a specific value from Column A (i.e., I inputted "Data2" from Workshee...

From ListBox selection >labels on userform to show sheet cell valu
I have 3 Userforms; the first contains a ListBox from which user select a specific Quotation number (column A on underlying worksheet). The first UserForm also contains text boxes that, upon a "cmdShow" button go and get relevant data from the row chosen by the user in the ListBox and show these values in the textboxes on UserForm1. All fine. On Userform2, I do not want user to have to choose Quotation number from the same ListBox (also on Userform2) in order to populate the textboxes on UserForm2 (different cell values from same row as User selected when on UserForm...

Setting a conditional value in a query to perform an additional qu
Ok, I have a slight brain twister here and wondering if anyone could help shed some light on this. :) Im in the midst of creating a database that will hopefully replace a paper-based system of storing 'shift runsheets' from a 24/7 office. There is 3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and Night (10pm to 6:30am). Each day it adds a new row of data to the table "shift" and users can peform searches on previous sheets by searching the table "previous" which links to "shift". When a user wishes to search, they select the ...

Create value list from a range of values
I've got a range of cells containing state abbrev. that I want to lis in another range excluding all duplicated states abbrev's. Is there a way to list the non-duplicated values automatically with function? I've tried using the excel Data Filter menu but it seems that needs t be performed manually. I need a function that automatically calculate when there is a change or addition to my list of states. Thanks for your help.. -- mallets12 ----------------------------------------------------------------------- mallets123's Profile: http://www.excelforum.com/member.php?action=...

Not all versions of fonts installed showing up in Publisher...
I want to use Trebuchet Italics--not the style tool on plain Trebuchet, but it doesn't show up as a choice in Publisher. I checked and the Italics version is installed on my computer. How do I get it to show up in Publisher? Thanks! Lori On the Font Schemes task pane, click Styles and Formatting, select what formatting you want to change and click the italic look. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "lori" <lori@discussions.microsoft.com> wrote in message news:03A14085-D0F9-4FDC-AB23-BAA1DCE983E3@micr...

Maintain cell reference after value is moved
How can you keep a reference to another cell from changing even if the other cell's contents are moved? The objective is to maintain a column of running summations, such as a projected bank balance, that refer to anticipated credits or debits in another column, even as those entries are moved about. For example, cell G100 has the formula =SUM(D$4:D100), which works only until the value in D100 is moved, say to D150, whereupon the formula in G100 is automatically changed to =SUM(D$4:D150). I can copy and paste the value to D150 and then just delete the old value in D100...

Add values of cells
I need to sum the values of a column of cells with both positive and negative numbers, but I want to exclude the negative numbers? Use: =SUMIF(D2:D5,">0") With D2:D5 replace by your range. -- Message posted from http://www.ExcelForum.com Hi try =SUMIF(A:A,">0") -- Regards Frank Kabel Frankfurt, Germany B2 wrote: > I need to sum the values of a column of cells with both > positive and negative numbers, but I want to exclude the > negative numbers? ...

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

Default Value in Table 02-22-10
I need a default value in my table to be the date value of another field in the table + 25 days. When I try to place this in the default value box, it says there is a type mismatch. ="ANLDate" + 25 Both fields are short date format. Any help is greatly appreciated. Cora, What you are trying to do cannot be done at the table level. You will need to do that on the form level. Is there any particular reason you are doing this in a table? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!"...

only one letter shows typing shortcut name
Windows XP pro SP3 This isn't really a big problem. I'm just curious about what is going on. Recently I notice that typing in a name (or changing a name) for desktop shortcut does this: The first three letters show up, then only one letter (the letter just typed) shows as the typing continues. The field expands as the name is typed but only one letter is visible as it is typed, until I am finished and hit return. Then the whole name shows up. Again, no big issue. Just curious. Any thoughts appreciated. Jack ...

Batch Status value
I understand that an article in the knowledgebase (ID 891318) describes the meaning of the Batch Status values. But I no longer have access to this information. Can someone please summarize the values? All I want to be able to do is retrieve the batch number of the current open batch on a particular register. Thanks, Steve P. I believe that would be zero, but it would be easy enough to check... In POS, open the Journal - the batch number should be displayed. Now do a query for that batch number and check the status. Now do a query for the status value you found - there should be one rec...

Duplicate Values in the index, primary key or relationship
Could you tell me what I need to do to get this not to give me an error? The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the date in the field or fields that contain the duplicate date, remove the index, or redefine the index to permit duplicate entries and try again. thanks. -- Denise B. Denise As the error message suggests, your table has data that would invalidate a key, an index or a relationship. You'll need to find/fix that duplicate data before re-tryi...

ListBox Value One form to another without filter applied
Hi All Access 2007 I have a form with a ListBox called "ListContactTasks" DoubleClicking the ListBox opens another form called "frmContact" at a particular record corresponding to the one in the listbox Column(0) The ListBox is on a form that has no recordset of it's own. Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmContact" stLinkCriteria = "[ContactID]=" & Me! [ListContactTasks].Column(0) DoCmd.OpenForm stDocName, , , stLinkCriteria My problem is that it takes me to the correct reco...

Inbox (envelope) Icon no longer shows reply or forwarded arrow
The Icon that shows whether a message has been read (envelope open) or not (envelope closed) no longer shows the replied or forwarded notification arrows. How do I get them back?!? ...

SUM in column with #N/A showing
Hi I have Excel 2003, I have a spreadsheet where the intent is to load various formulae and have them costed, there are 30 rows but not all are used each time. I have a VLOOKUP reporting the cost to a column where I need to total the cost but the SUM reports #N/A unless I have each row filled and a cost showing in each cell. How can I overcome this? thanks Daniel Maybe you could use a formula like: =SUMIF(a1:a30,"<>#n/a") or change the =vlookup() to hide those errors: =if(isna(vlookup(...)),"",vlookup(...)) Daniel wrote: > > Hi > > I hav...

Returning Multiple values from A Vlookup
I need a formula to return multiple values from a look up table as the number appears multiple times in a column. Reference cell is $C$6 eg = 4101 Current formula only return the first one it finds =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE))) I need the Cell to return all the values it finds from the one code eg = QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW Lookup table is TNI (A1:C1053) I need it to return all values in Column (C) A B C 4077 All suburbs QRLE/QRLD 4078 Forest Lake QRLD/QLGH 4101 H...