how to have result field blank

In a workbook, have a data entry sheet that is protected except for fields 
for answers.  These fields (shaded yellow) are formatted as accounting.  The 
yellow fields on the Data Entry worksheet populate other cells in the Webinar 
Cost Calculator and Webinar ROI Calculator worksheets.  If a cell on the Data 
Entry screen is blank, I want it to return a blank, not a 0 or a -, which 
it's currently doing.   Can excel format the result when there is no data to 
return a blank?
0
Ellen (52)
12/13/2005 9:41:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
576 Views

Similar Articles

[PageSpeed] 20

Use a custom format so that a zero value generates nothing.  If you want the 
accounting format, with this one exception, set the accounting format.  Then 
Format > Cells.  On the number tab select Custom category.  In the text box 
you'll see the incantation that equates to the accounting format.  Clear the 
part following the second semicolon and click OK.

"Ellen" wrote:

> In a workbook, have a data entry sheet that is protected except for fields 
> for answers.  These fields (shaded yellow) are formatted as accounting.  The 
> yellow fields on the Data Entry worksheet populate other cells in the Webinar 
> Cost Calculator and Webinar ROI Calculator worksheets.  If a cell on the Data 
> Entry screen is blank, I want it to return a blank, not a 0 or a -, which 
> it's currently doing.   Can excel format the result when there is no data to 
> return a blank?
0
12/13/2005 9:42:14 PM
Not sure if I completely understand, but you can have a blank result in a 
cell as part of your formula.  Just set a condition (IF statement).  Like so:

Let's say we want cell B3 to have the result of A1/B1, but only if B1 has a 
value in it.  The formula in B3 would be =IF(B1="","",A1/B1)

It breaks down like this =IF(KeyCell=Nothing,DoNothing,OtherwiseCalculate)

Make sense?

Let me know if you have questions.



"Ellen" wrote:

> In a workbook, have a data entry sheet that is protected except for fields 
> for answers.  These fields (shaded yellow) are formatted as accounting.  The 
> yellow fields on the Data Entry worksheet populate other cells in the Webinar 
> Cost Calculator and Webinar ROI Calculator worksheets.  If a cell on the Data 
> Entry screen is blank, I want it to return a blank, not a 0 or a -, which 
> it's currently doing.   Can excel format the result when there is no data to 
> return a blank?
0
12/13/2005 9:42:48 PM
Reply:

Similar Artilces:

Please help me decipher the result of COUNT.
Following data from A1:A7 Sales 12/8/2008 19 22.24 TRUE #DIV/0! =COUNT(A1:A7,2) returns 4. I don't understand where the four occurrences of 2 come from. 22.24 = 3 occurrences 12/8/2008 = 2 occurrences total of 5?? I know I am missing something. Please tell me. Is there a tool that I can use to trace? I tried the formula auditing toolbar, no luck because there is no error and trace precedents is not detailed enough. Appreciate explanation. Epinn have a look in the help index for COUNT -- Don Guillett SalesAid Software dguillett1@austin.rr...

Read-only fields based on security role
On a given tab, certain fields I want to be read-only for most users, except for roles "system admins" or "system customizers". These roles would be the ones needing to update the fields periodically. Is there an easy OnLoad js for this? I know how to make fields read-only and/or disabled on a global basis, just not on a peruser or security group level. The c360 field security addon would definately do what I need, but is too expensive to justify for us. Thanks. There is no easy onload script for this, but you could write javascript that calls a web service wh...

If statement result appearing in different colors
Is there a way to write an IF statement where the text will change color depending on the result? eg =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' would appear in RED, etc Thank You! WayneD Take a look at Format|Conditional formatting. WGD wrote: > > Is there a way to write an IF statement where the text will change color > depending on the result? eg > > =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' > would appear in RED, etc > > Thank ...

Conditional Formatting using result of Cell Formulas
Hi, I'm trying to make a conditional format when the result of a formula results in a value being entered in a cell. I'm using Excel Xp and my formula is as follows. =IF(F160="Need Info",TODAY()-S160,"") I've tried using formulas like >0, <>"", <>BLANK etc. but they all seem to fail. All cells are formatted, even entirely blank cells, as if the condition has been met. BTW, is this formula better strategically than the above formula. =IF(F160<>"Need Info","",TODAY()-S160) Just curious. TIA!!! Norm If ...

sql result different to excel result
Hi, Just wondering why I am having such problems rounding figures, when I round the same figure on an Excel Sheet, I end up with a different end result to what appears in sql. The Excel Looks Like This. Net Vat Total Allocated Result (=(NET+VAT)/Allocated*Total) 900.00 157.50 1721.38 1721.38 1057.50 150.00 26.25 1721.38 1721.38 176.25 The Result field is a calculated field and is rounded to 2 decimal places, the calculation is in brackets next to the title name. The Table in SQL has the same structure but the net, vat,...

Count results from query with no results
I have continous form with an unbound text box [keyword] and a button [search]. There is also an unbound textbox [numberofresults] that counts the number of records returned. If the search produces no results, the [numberofresults] needs to read 0, instead it is just blank. What is the proper way to show zero results as 0 and not blank? It depends on how you are doing your search? Do you have a function? Are you just filtering the recordset? Dale -- email address is invalid Please reply to newsgroup only. "Ryan Tisserand" wrote: > I have continous form with an un...

Formula to return numerical, text result?
I need to create a formula that based upon three possible factors, wil return one of two calculations or a text message. If cell B="Good", the formula in cell C returns "Text" If cell B="Better", the formula in cell C returns cell A*.3 If cell B="Best", the formula in cell C returns cell A*.5 Thanks in advance for any tips -- burgeo ----------------------------------------------------------------------- burgeon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2023 View this thread: http://www.excelforum.com/showthread.php...

How to replace a function with its result or resulting reference in a formula?
Hello How to replace a function with its result in a formula? For example, =INDEX(...)+INDEX(...) with =result_of_INDEX(a...)+result_of_INDEX(b...)=1025+1307. How to replace a function with its resulting reference in a formula? For example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 cells automatically. "Dmitry Kopnichev" <kopn@bk.ruDelete> �������/�������� � �������� ���������: news:%23cF5PL9zFHA.916@TK2MSFTNGP10.phx.gbl... > Hello > How to replace a function with its result in a formula? For example, > =INDEX(...)+INDEX(...) with > =r...

GPS Coordinates Field
Good day, Seems like default addresses have a longitude and latitude field within MSCRM 1.2. Unfortunately a float data type has been configured which does not allow for modification. The format of a coordinate would be something like Of course we could enter our own custom field but with MS Map Point being a future consideration we would like leverage from this by using captured data in the correct format... Depending on accuracy required we could have values such as Redmond being 47-40'27.32 The only allowable format wraps the numerical value entered into something in the fashion o...

prefix a field
How do I add a prefix to a field. Is this for printing purposes? If so, use a custom number format. "lee" wrote: > How do I add a prefix to a field. ...

Change Required Field Property in Table
I am trying to change the required field property in for a field in an existing table based on a value in a field in my form. I am using the BeforeUpdate event. Here is what I have so far: tables!tblPayments!tblPaymentsDateWritten.Required = False When the conditions are met, I get a message that says, "Runtime error '424' Object Required." Can the Required property of the field be altered from a form? If so, How can I do it? "Jonas" <sundet@yahoo.com> wrote in message news:68bdf43b-85b9-4ad0-8a38-faee98d6dabf@d2g2000yqa.googlegroups.com......

Hide results
I am putting together a summary spreadsheet of dates from seperate worksheets in an excel file. In the summary sheet I reference the 'end' date from the summary sheet. If there is no date in the original file, what formula can I use to leave that cell blank/hide on the summary sheet? The way I have it now the results show the default of 1/0/1900 Here is the formula used to reference the info from. =Summary!K5 =IF(Summary!K5="","",Summary!K5) -- David Biddulph "bigred72" <bigred72@discussions.microsoft.com> wrote in message ...

Count and summarize text fields dcountda/pivot?
Hi, I am new to advamced excel, any other then sum of colums:) I am helping a friend to make a planning scheme fo here shop, monday - sunday. A list of fixed tasks distributed between 10 employee 9-21 monday-sunday. I'd like to summarize the distribution of task/day, task/week on a total and per emplouee (A,B,C,D..). task is represented below by k1,sk,fg. count(task) x 1/4 hour = time/task. I have tied DCOUNTA and pivot table but did not manage:( Any help is appriciated! I have the feeling it's basic for you guros:) Lars mon tue wed ... Ti...

Rounding actual results
Hi, I've got a grid that says: Hr Min Maths: 5 25 English: 7 25 And so on. I'm wanting to work out how much time is devoted to learning in the whole week. What I've got so far is: Hr Min Total learning time: =A1+A2... (counts up all totals in hours column) My current answer for hours is 18. I'm stuck with what to do with the minutes column. so far, I've got an ...

Mapping Picklist Fields 02-21-06
I am trying to map between the account industry field and a custom field in the accounts contacts. I have set up the same fields in both picklists but when I create a new contact I ony get the underlying value equal to the value in the accounts picklist. I suspect you nned to set up the firled with teh same underlying valuses but how do you do that. In the accounts list the values start at 34 whilst in teh contacts they start at 2. Anyway to set the underlying value? This is not supported, but you can do it by editing the exeported customizations for the table. First, export just...

Business Portal 3.0
Hi, I cannot see any Project related data in the Business Portal Queries. I have checked, double-checked and triple-checked security, and all seems valid. My user is a Project Manager and Project Approver. The Project Manager role definitely has Data Permissions access to all the Project objects. There are about 142 projects in total. If I click in Project-Queries-Projects, I get the message: Query Returned No Results Any ideas? Regards Konrad ...

Grouping Data Fields
Can the following be accomplished in Access 2K3? Name City ABC123 Tampa ABC123 Orlando ABC123 Miami I want to group on name and get the following result in City field or add another field to store the combined city names. ABC123 Tampa, Orlando, Miami On Mon, 15 Feb 2010 21:34:01 -0800, NEWER USER <NEWERUSER@discussions.microsoft.com> wrote: >Can the following be accomplished in Access 2K3? > >Name City >ABC123 Tampa >ABC123 Orlando >ABC123 Miami > >I want to gro...

Formula Result is different from the shown result
In the Function Arguments dialog the official Formula Result is different (and wrong) from the result shown after "=" just under the Serial number field. I am using the Year formula. Whatever formatting I use for the date neither this formula nor Text to Columns can return the correct year. EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial number field it shows the correct result of = 2007 Dan, This question; at least to me, isn't clear. What are you entering and where and what is the formula you are referring to? If you enter something ...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

Displaying and hyperlinking search results in MFC app
I have an MFC (MDI) app that was written using Visual Studio 6. The app retrieves some search results from the database and I want to display those to the user. From the search results I want the user to be able to click via hyperlink to pull up the item. Within MFC what is the best way to display this data? The requirements are: 1. I want to be able to include a small icon for each seach result. 2. I want to include some descriptive text and have the ability to change the colors of that text. 3. I need to be able to trap the clicked event on the hyperlink. 4. It would also be nice if ...

Verify value overwrite in Auto Filled fields
I have several fields on my form that are auto populated by selections made from a combo box. One of these fields is a phone number field. A user MAY enter data into the phone number field BEFORE selecting the corresponding info from the combo box. Once they select the info from the combo box the value they entered in the phone number field is overwritten with that which is auto popluated from the combo box. Is there a way to prompt user if they want to overwrite value in the phone number field??? This form is used for our tech service department to log service calls. The majority o...

how to put an internet-address in Excel just as a text-field?
When I make a table in Excel, I have one column for web-addresses. These become links that I get connected to every time I try to copy the content of the fields, but I just want it to be a text-field. How do I disconnect the automatic link-function? Thank you. type a ' (single quote) before http:// HTH -- AP "Tomas Barth" <Tomas Barth@discussions.microsoft.com> a �crit dans le message de news: 18371ACA-7CF3-4A89-B492-582C70AA2F8F@microsoft.com... > When I make a table in Excel, I have one column for web-addresses. These > become links that I get connected to ev...

If Statement in textbox if the results show as #Error
I have a textbox where its doing % change from two other textboxes. And sometimes the other two other textboxes have 0 as values and it won't be able to do the calculation. I am getting two errors...#Error and #Num!. How can i fix that so it shows blank instead of the #Error and #Num!..... Thank you, Jay You need to change your formula to cater for the possibility of zero (or null) in the other textboxes. Without knowing your formula, I can't specify what to do. -Dorian "Jay" wrote: > I have a textbox where its doing % change from two other textboxes. And &...

Cannot add a field I want.
Trying to add statuscode to my Lead form. It is already on my Opportunity form. There it is in the Lead Attributes but it doesn't show when I try to add it to my Lead form. I tried mapping it first to statuscode in the Opportunity object. I tried it unmapped. The status typically is already on the Administration page. If it's not appearing on the list when you click "Add Field" it's because it is somewhere on one of the tabs already. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Tue, 16 No...

No result!
Hi , i have a query, sometimes the formula doesn't yield the result which it should, even simple things like =d2-c2. The result is "=D2-C2" while if i try it in some other cell or say other worksheet , it works fine! Any ideas? -- Jim India Hi Jim, Your cells were formatted as text before you entered the formula. Format as General and re-enter the formula (F2, ENTER) -- Kind Regards, Niek Otten Microsoft MVP - Excel "Jim" <kunalbhardwaj@msn.com> wrote in message news:eVtV%23MfvEHA.1260@TK2MSFTNGP12.phx.gbl... > > Hi , i have a query, some...