Tweaking cells

If I have cells that may remain blank, how do I get them to be blank instead
of showing a 0? These cells are set for text, not numbers, so I don't
understand why a number would show there by default.

TIA

Steven Connor


0
none3534 (52)
2/24/2004 3:02:37 PM
excel 39879 articles. 2 followers. Follow

2 Replies
476 Views

Similar Articles

[PageSpeed] 50

Hi
do you have formulas in these cells and if yes what kind of formulas?

--
Regards
Frank Kabel
Frankfurt, Germany

**{Steven}** wrote:
> If I have cells that may remain blank, how do I get them to be blank
> instead of showing a 0? These cells are set for text, not numbers, so
> I don't understand why a number would show there by default.
>
> TIA
>
> Steven Connor

0
frank.kabel (11126)
2/24/2004 3:09:50 PM
Nevermind, F1 to the rescue. You would think that after all this time, I
would have learned to search help before posting. I guess I still have some
lazy habits left.

Steven

"**{Steven}**" <none@myisp.com> wrote in message
news:%23gFD8cu%23DHA.2524@TK2MSFTNGP10.phx.gbl...
> If I have cells that may remain blank, how do I get them to be blank
instead
> of showing a 0? These cells are set for text, not numbers, so I don't
> understand why a number would show there by default.
>
> TIA
>
> Steven Connor
>
>


0
none3534 (52)
2/24/2004 3:13:22 PM
Reply:

Similar Artilces:

Having info in a certain cell EXACTLY same as in cell on anotherpa
SIMPLY... I want in my summary tab to have the data from A1-P30 to ALWAYS be the data showing on my first page tab that is in position A1-P30.. Once a week I move the data in my first page by moving rows up and down depending on the performance of a certain golfer and what place he now is in overall. This is driving me nuts! I know all about =page1$C$8 but that doesn't seem to work... "Max" wrote: > In your summary tab, > place this in any starting cell, say in B2: > =IF(ISBLANK(OFFSET(INDIRECT("'page1'!A1"),ROWS($1:1)-1,C...

Sorting Area with Merged Cells
In the area A23:G30, each row contains merged cells of column B, C, D, E and F apart from each other row in the area. I get a "This operation requires that merged cells to be identically sized" error when running this macro. Even using the Data/Sort from the File menu gives me this error. How would I sort an area containing rows with merged cells? Also, the cells must remain (or return to being) merged after this operation. I appreciate the previous help concerning clearing the contents from merged cells using ActiveCell relative references. Unfortunately, my email/newsgroup ...

Linking two cells in two books
Hi, I have two workbooks. In Book1, I want A2 to be exactly the same as B1 in Book2. The problem is that every night, I run a program which updates Book2, inserting a new row 1, so the value in B1 is now pushed down to be the value in B2 and a new value is inserted into in B1. I want this new value in B1 to be reflected in A2 (Book1). That is, for A2 (Book1) to always be the same value as B1 (Book2), even when B1 becomes a new row with a new value. I thought I would be okay if I used dollar signs, so for A2 in Book1, I have: ='[Book2.xls]AA'!$B$1 But it doesn'...

Copying the column heading into all non-empty cells #3
Gord you're a genius! Thanks so much - that works a treat! Sorry I didn't get back to yo last night - I'm in London and it was past my bedtime. The reason I'm copying the headings into the rows by the way, is that have a database which I want users to be able to search, and it's easie if I just have have one column listing all the Amenities for each hotel rather than 120. Incidentally, my next move is to now merge all the cells in each ro into one, with the amenities separated by spaces. I've just downloade a "Merge Cell Wizard", which has accomplished th...

Sum of cell which use if array function
Hi, I have many cells using if function which result is number. I'd like to sum all of those cells, unfortunatelly i cant do that, the result is none. How can i solve this one? Thank you If your IF formulas contain quotes around numbers, REMOVE THEM. Wrong: =IF(A1="x","10","0") Right: =IF(A1="x",10,0) -- Biff Microsoft Excel MVP "Pran" <Pran@discussions.microsoft.com> wrote in message news:1F9DB21C-4DF6-45A5-AA22-D3F8CDA45F31@microsoft.com... > Hi, > > I have many cells using if funct...

Can you automatically add a comment to a cell
Hi all Is it possible to automatically add a comment to a cell once it has been selected. The cells currently have data validation to only allow you to enter certain letters and conditional formating to change the colours once these letters have been selected. I would like it to add the current date and time when any of the letters have been selected using the drop down box through data validation. Thanks Mally Private Sub Worksheet_Change(ByVal Target As Range) Target.ClearComments Target.AddComment ("Date and Time Last modified " & Now()) End Sub "Mall...

How to return a blank cell if sum = 0
How would I show a blank cell, instead of the number 0 in the cell, if the formula in that cell results in an answer of 0? =If(myformula=0,"",myformula), say, +if(sum(A3:g3)=0,"",Sum(a3:g3)) Peter "waybomb" wrote: > How would I show a blank cell, instead of the number 0 in the cell, if the > formula in that cell results in an answer of 0? > How would I show a blank cell, instead of the number 0 in the cell, > if the formula in that cell results in an answer of 0? Another option... if you want zero values to not show **anywhere** on your worksh...

Running a mcro based on value of a cell
I have a spreadsheet that performs End Of Day calculations on Financia Market instruments. I have a large macro that runs at the end of every day,part way throug the macro i need to collect data from a column in a worksheet based o the text that appears in cell T1. In the YEARLY sheet i have columns of data that have the end of yea prices of various Financial instruments. In row 1 across the worksheet are the names,e.g Vodafone,Do Jones,Nasdaq,GBP/USD etc.Below each name is a column of 20 year data,i.e A2:A21,B2:B21,C2:C21 etc. The macro needs to look at the text in T1 and then find the a...

Cell update delay
Hi, I have a spreadsheet with two cells that are linked to an external value. However I want one of these cells to only update every second. This is basically so I can see the difference between what the external value is now, and what it was a second ago. Any advice? Thanks Take a look at this: http://www.cpearson.com/Excel/OnTime.aspx If you do it once per second, it will (probably) take over control of your system. Good luck!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bob_hc" wrote: ...

Conditional copy cells from one excel file into current
Hello All, Does a macro (or function or ??) exist to conditionally copy value from a stored spreadsheet into the current (open) spreadsheet? Here's the scenario: Stored spreadsheet is two columnsof text (~200 rows) : Think explici question in column one, then the correct answer in column two of th same row. Then my current (open) spreadsheet has SOME of the same questions i column one. I would like to invoke a [macro?] that, in pseudo code, does somethin similar to the following: for each row (question in column 1) of the current spreadsheet Search in column 1 of *stored* spreadshee...

extract covert dollar value in text cell to just dollar value
cell value/contents: 10 for $1,700.00 to just $1,700.00 for Cell A1 use =MID(C29,FIND("$",A1),72) this will extract everything to the right of (and including) the $ sign -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11273 View this thread: http://www.excelforum.com/showthread.php?threadid=275359 On Thu, 4 Nov 2004 10:08:06 -0800, "Kelly" <Kelly@discussions.microsoft.com> wrote: >cell value/contents: 10 for $1,700.00 to ...

how to activate a cell if it contains data
I've created a Pvt Table which contains 6 fixed columns and it will show data depends on how many columns I wanted to see. In addition, I've created another set of columns to copy data shown on the pvt table. So here is my question, is there a way to activate cells with data on the Pvt Table? Here is an example: If I wanted to see 4 columns of data on the Pvt Table: Column A Column B Column C Column D Column E Column F Row1 $27 $30 $49 Total My other Set of columns should appear like this: Colum...

How do I automatically fill a cell based on another cell in Excel
I am trying to fill an adjacent cell based on the input in another cell. Here are the particulars. I have created a pull down list in one cell. The list contains all of the parts that our mechanics order. Now when one of the parts are chosen I would like for Excel to populate the next cell over with the price of the part automatically. Can this be done? Yes, I assume you already have a price for every part in the list somewhere. So what you need is a VLOOKUP formula. =VLOOKUP(A1,your range, 2,0) A1 is the cell with the drop down list your range - range where you have your part list...

Preserving formatting of merged cells when hiding columns
I have a spreadsheet that contains columns that my supervisor wants to keep hidden when sending out to contacts. The problem is, there are some merged cells further down the sheet that spread across those columns, and hiding the columns messes up their formatting. Is there a way to avoid this? merged cells are the bane of Excel, figure out how to do without them. Nick Xylas wrote: > I have a spreadsheet that contains columns that my supervisor wants to > keep hidden when sending out to contacts. The problem is, there are > some merged cells further down the sheet that spread across ...

Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

formula in cells
I'm going to try and explain this a little better and give you the actual cells that I am trying to calculate: I have net pay in cell B8, withdraws in cell C8 and the Balance in D8. I would like row D to add B, subtract C and display the balance. So when I put in the next paycheck on B9, D9 will reflect the amount from D8, add to it B9, subtract C9 if a value is there and display the balance in D9. I would like the cells in Column D for the last value entered to be blank until data is entered into them and not display the last balance the whole way down the spread sheet. Does this...

how to make cells automatically format to entered text size?
hello, i am new to excel and i would like to have it resize the cells automatically to the size of the text i entered. i know if i click on a cell and do format - line break it does it for one cell. but how can i have excel do this automatically for every cell in every workbook? thanks Frank Barenhorst email: frank.barenhorst@mailshelter.de Frank Hit CRTL + A to select all cells. Format>Alignment>Wrap Text. To make this the default for all new workbooks and sheets.... Open a new workbook. Customize as you wish. File>Save As Type: scroll down to Excel Template(*.XLT) and...

Automatically Hyperlink Text in certain cells?
Anyone know if it is possible to automatically hyperlink. i looked under format cells but no such option. for example if i type: testfile.zip into Cell A1 it will automatically make the text a hyper link to testfile.zip tia You have a couple of options: #1. You could type in a longer string: file:////c:\my documents\zipfiles\testfile.zip (include the "File:////" and the full path to the file.) #2. You could use an adjactent cell that uses a worksheet function: =if(a1="","",hyperlink("file:////c:\my documents\zipfiles\" & a1, "Click m...

Cell Echoing Help in VBA
Is there a VBA routine that will echo back the cell location based on the value in another cell ? For example, consider the following A B C D 1 101 110.5 2 102 3 103.9 4 104 5 110.5 6 121 I'm looking for the cell location for the value (110.5) contained in cell C1 and place the returned location (A5) in cell D1. Hi Dave To use a formula on the worksheet.... In cell D1 enter =IF(COUNTIF(A:A,C1)>0,"A"&MATCH(C1,A:A,0),"No match") Using code.... Sub test() If Application.CountIf(Range("A:A"), Range(&q...

Adding fill to cells only when value is "No" in another cell
I would like to apply a conditional format to a selection of cells that will change the fill to gray when the value in another cell = "No". So, let's say "No" was input into cell A1, if so then cells B1 through E1 would automatically fill with gray. If the value in A1 is blank or "Yes" or anything else, then no formatting would occur in those cells because data would need to be input in them. Is it possible to do this? In B1, create a conditional format of: =$A$1="No" and set the fill to gray. Now copy the format of B1 down to E1 ...

formating multiple cells
I have an spreadsheet froma data collector that has roughly 42,000 rows and 15 columns of data. Unfortunetly the data collector formats the cells to be "text" instead of "number". When i go to make this change by selecting all the cells and changing there format this process can take up to 30-45 minutes. Now this is on a p4 with 2gb of ram. However i do this same change on my laptop (much slower) and it happens in a matter of seconds. What gives....why would my work PC be slll slow. I am running Office 2003 at work and Office XP on my laptop. Hopefully someone ...

Protect Cells
I have Psion Series 3MX that has spreadsheet software called Sheet. In Sheet, I can Protect individual cells, so that another user can't alter its content, but leave specific cells unprotected for the user to enter their own figures. How can I do this with Excel. I have tried, but seem to only be able to Protect the whole sheet and not leave individual cells unprotected? -- Many Thanks Enquirer If referring to MS Excel workbooks/worksheets.......... By default, all cells are locked when you protect the sheet. Select the cells you wish to leave unlocked for entry. Format>...

Display contents of cell in another cell as part of text string?
How can I display the contents of a cell in another as part of a text string. Such as... "The answer is "B4"" where B4 is the contents of cell B4? Thanks Try this: ="The answer is "&B4 Note <space> after "is". -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== <mschmidt@carolina.rr.com> wrote in message news:1152383541.185407.241490@m79g2000cwm.googlegroups.com... How can I display the content...

How do I put a blank value in a cell not using "" in a formula
Thanks in advance I have a row that that is sometimes blank or contains a number followed by a space and two letters eg. In cell A1 I have the value 456 CR In cell B1 I am using the formula =IF( ISBLANK(A1),0,(LEFT(A1, LEN(A1)-3)*1)) to strip the space and two letters from the cell. I multiply (LEFT(A1, LEN(A1)-3) by 1 to convert to a number from text. I am almost happy with the formula. I want the first part of the IF statement to evaluate to 0 if the field is blank but do not want to see the 0. If I use "" instead of 0 it is a text value again which causes nu...

Locating a specific cell
Hi This is a simplified example of what I am trying to do I want to display the result for the max and min and also the year and month. In this case the min is 2 in Feb 2009 and the max is 9 in Mar 2010 A B C D E 1 Jan Feb Mar 2 2009 3 2 7 3 2010 5 4 9 -Regards bookman Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can not...