Ignore Blank Cells

I update a workbook weekly in separate worksheets.  My master worksheet links 
to the appropriate cells for the updated data.  These are simple percentage 
numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
problem I have is that if one of the cells is blank, it returns a 0 and I 
need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
with so I really don't want to have to update manually all blanks.
0
Utf
4/27/2010 5:53:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
733 Views

Similar Articles

[PageSpeed] 16

Hi SEF,

As long as you want to hide all zero values on a sheet you can use in
excel 2003
From the Tools menu select Options
On tab View uncheck Zero Values

If you only would like to hide the zeros from the formula results you
can use conditional format for the result cells.
How this  works depends on the excel version you arre using.
Look at the online help.

An other option is to use the number format for those cell with
something like:
#.##0_);[Red](#.##0);[White]0;[Blue]@

HTH,

Wouter

0
Wouter
4/27/2010 6:18:23 PM
If linking formulas are like  =Sheet1!A1  change them to

=IF(Sheet1!A1="","",Sheet1!A1)

Copy down and across.

Or use a macro to change all existing formulas.

Sub ZeroTrapAdd()
Dim mystr As String
Dim cel As Range
    For Each cel In ActiveSheet.UsedRange
        If cel.HasFormula = True Then
            If Not cel.Formula Like "=IF(*" Then
                mystr = Right(cel.Formula, Len(cel.Formula) - 1)
                cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
            End If
        End If
    Next
End Sub


Gord Dibben  MS Excel MVP

On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF@discussions.microsoft.com>
wrote:

>I update a workbook weekly in separate worksheets.  My master worksheet links 
>to the appropriate cells for the updated data.  These are simple percentage 
>numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
>problem I have is that if one of the cells is blank, it returns a 0 and I 
>need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
>with so I really don't want to have to update manually all blanks.

0
Gord
4/27/2010 6:24:41 PM
Probably the simplest way is to go into the display options for Excel 2007 
and uncheck the box [Show a zero in cells that have zero value]; it's in the 
Excel Options that you access at the bottom of the Office Button menu, In 
Excel 2003, it's found in the Tools>Options>View menu (it's a check box that 
says Zero values). If these are NOT checked you shouldn't see the zeroes in 
the cells linked to empty cells. You may have to re-open the file for it to 
take effect.

HTH
Bill

"SEF" wrote:

> I update a workbook weekly in separate worksheets.  My master worksheet links 
> to the appropriate cells for the updated data.  These are simple percentage 
> numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
> problem I have is that if one of the cells is blank, it returns a 0 and I 
> need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
> with so I really don't want to have to update manually all blanks.
0
Utf
4/27/2010 6:40:02 PM
I should probably have mentioned that in 2007, it's under Excel 
Options>Advanced>Display options for this worksheet.

Bill

"SEF" wrote:

> I update a workbook weekly in separate worksheets.  My master worksheet links 
> to the appropriate cells for the updated data.  These are simple percentage 
> numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
> problem I have is that if one of the cells is blank, it returns a 0 and I 
> need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
> with so I really don't want to have to update manually all blanks.
0
Utf
4/27/2010 6:42:01 PM
Thank you all - the macro was especially helpful!

"Gord Dibben" wrote:

> If linking formulas are like  =Sheet1!A1  change them to
> 
> =IF(Sheet1!A1="","",Sheet1!A1)
> 
> Copy down and across.
> 
> Or use a macro to change all existing formulas.
> 
> Sub ZeroTrapAdd()
> Dim mystr As String
> Dim cel As Range
>     For Each cel In ActiveSheet.UsedRange
>         If cel.HasFormula = True Then
>             If Not cel.Formula Like "=IF(*" Then
>                 mystr = Right(cel.Formula, Len(cel.Formula) - 1)
>                 cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
>             End If
>         End If
>     Next
> End Sub
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF@discussions.microsoft.com>
> wrote:
> 
> >I update a workbook weekly in separate worksheets.  My master worksheet links 
> >to the appropriate cells for the updated data.  These are simple percentage 
> >numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
> >problem I have is that if one of the cells is blank, it returns a 0 and I 
> >need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
> >with so I really don't want to have to update manually all blanks.
> 
> .
> 
0
Utf
4/27/2010 8:14:01 PM
Thanks for the feedback.

Gord

On Tue, 27 Apr 2010 13:14:01 -0700, SEF <SEF@discussions.microsoft.com>
wrote:

>Thank you all - the macro was especially helpful!
>
>"Gord Dibben" wrote:
>
>> If linking formulas are like  =Sheet1!A1  change them to
>> 
>> =IF(Sheet1!A1="","",Sheet1!A1)
>> 
>> Copy down and across.
>> 
>> Or use a macro to change all existing formulas.
>> 
>> Sub ZeroTrapAdd()
>> Dim mystr As String
>> Dim cel As Range
>>     For Each cel In ActiveSheet.UsedRange
>>         If cel.HasFormula = True Then
>>             If Not cel.Formula Like "=IF(*" Then
>>                 mystr = Right(cel.Formula, Len(cel.Formula) - 1)
>>                 cel.Value = "=IF(" & mystr & "="""",""""," & mystr & ")"
>>             End If
>>         End If
>>     Next
>> End Sub
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Tue, 27 Apr 2010 10:53:01 -0700, SEF <SEF@discussions.microsoft.com>
>> wrote:
>> 
>> >I update a workbook weekly in separate worksheets.  My master worksheet links 
>> >to the appropriate cells for the updated data.  These are simple percentage 
>> >numbers (not forumlas, etc.) and only need to be a one-on-one link.  The 
>> >problem I have is that if one of the cells is blank, it returns a 0 and I 
>> >need to to stay blank.  Any ideas?  There are about 57,000 cells I am working 
>> >with so I really don't want to have to update manually all blanks.
>> 
>> .
>> 

0
Gord
4/27/2010 9:26:10 PM
Reply:

Similar Artilces:

Range Blank
Good afternoon, Is there a way to use an If statement to check to see if a range of cells is blank? For instance, I would like to use a formula to sum B2:B10 if there is data in at least one cell. But if all cells are blank, I would like the sum to show a blank instead of an 0. Something like, If (B2 OR B3 OR B4...)<>"", Sum B2 to B10, Else leave blank. Any suggestions? Thanks. Cordially, Hi! One way: =IF(COUNT(B2:B10),SUM(B2:B10),"") Biff "Brent E" <BrentE@discussions.microsoft.com> wrote in message news:542AE08D-B23B-4C03-BCA9-53054EC65BE...

Double-Clicking a cell for Multiple Options
I recently came across a spreadsheet where a cell was double clicked for the multiple options to become available. This was similar to a drop-down list, but there was no drop down list!! Confused? An example... The cell currently shows a value of "A". I double-click on this cell ... the cell changes to "B". The new value is not random, but coming from a pick list or a pre-determined list. Does anybody know how such a feature is created? Regards, andym If you rightclick on that cell and choose "Pick from DropDown List" do you get the same effect? If yes, ...

Blank Field
I transferred data from one excel file to another. Upon transferring the data as Value only, the new excel file is capturing a hidden character in the new sheet. Any idea how I can tell excel to ignore all blank fields on an excel spreadsheet ? "Eric" wrote... >I transferred data from one excel file to another. Upon >transferring the data as Value only, the new excel file is >capturing a hidden character in the new sheet. Any idea >how I can tell excel to ignore all blank fields on an >excel spreadsheet ? What's the hidden character, as in CODE(CellContai...

Cell shading based on date conditions
I want to shade a column based on a date criteria. The date criteria is entered by the user in column B5 and the day could be other than the 1st. I have 20 columns that display values and the date is displayed as heading. For example Column C Column D Column E Row 6 7/1/2006 8/1/2006 9/1/2006 Row 7 Row 8 1,000 500 Row 9 500 500 Row 10 500 500 Row 11 500 500 If the user enters 8/31/2006, I want Column D to be shaded. Column B5 changes every month. Is it possibl...

Avoiding returning a 0 when no data in cell
I have worked out the formula I need to use (with help from someone from this group). However how do I stop it returning a 0 when no data is present in the cell? My formula: =VLOOKUP($G37,Staffing!$C:$AL,36,FALSE) Many thanks Steve =if(vlookup(...)="","",vlookup(...)) You may want to suppress the #n/a error, too: =if(iserror(vlookup(...)),"",if(vlookup(...)="","",vlookup(...))) Steve wrote: > > I have worked out the formula I need to use (with help from someone > from this group). > > However how do I stop it returnin...

how do i convert 100 in one cell to one hundred in another cell
i want to convert number (that could also be result of a function) in one cell to words. Example if one cell has 121 then in another cell it should read one hundred twenty one. This should work http://support.microsoft.com/default.aspx?scid=kb;en-us;259663 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Numbers into words" <Numbers into words@discussions.microsoft.com> wrote in message news:B22E0C37-8CB8-427F-AACD-9D42B5046A6E@microsoft.com... >i want to convert number (that could also be resu...

issue with Ignore Other Applications option
I want to view my Excel workbooks in separate windows (I have 2 monitors and want different workbooks displayed in each). Looking around online it seems the setting to allow this is: Tools > Options > General > Ignore other applications I selected this, and it works. When I click on an Excel file in Explorer, a new instance of Excel opens. However, when Excel opens, there is no workbook. I have to go to File > Open and manually navigate to the file to edit it. Is this a known side effect or some anomaly? Is there anyway to fix this while still being able to open workbooks in se...

Referencing a cell even if its contents are moved moved/replaced
I want to reference a cell no matter what happens to it. Simple example: From A1 down: A1=B1 A2=B2 A3=B3 From B1 down: 5 10 15 If I cut and insert B3(15) into B2, the A column would then read as: A1=B1 A2=B3 A3=B2 How do I keep the A column referencing the original cell? =indirect("b1") will always point to B1 -- no matter if you delete or insert any rows/columns/cells. Darren wrote: > > I want to reference a cell no matter what happens to it. > > Simple example: > > From A1 down: > A1=B1 > A2=B2 > A3=B3 &g...

Including cells that are highlighted in a formula
I have a strange requirement maybe, but I do a budget spreadsheet on which I highlight items that have cleared my checking account. I would like to be able to write a formula that sums only the cells that are highlighted a certain color. Is this possible? let me clear this up a little. I don't really mean "highlighted" as in I highlighted them with a mouse. What I mean is formatted with a fill color. sorry for the confusion. "Robin" wrote: > I have a strange requirement maybe, but I do a budget spreadsheet on which I > highlight items that have ...

Strange cell behaviour #2
Hi all, There's a very strange problem i'm currently having when entering figures into any cell in any workbook. For instance, when i enter '1', the figure 0.01 is displayed, 10 = 0.1 and so on. I've tried cell formatting but to now joy. Any ideas? Alternatively, is there a way i can reset all excel default settings without having to re-install? Your help would be gratefully received. -- madhatter_scfc ------------------------------------------------------------------------ madhatter_scfc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

Multiple "IF" functions one one cell returned from different cells
OK, maybe that wasn't the best way to ask the question... I need to create this scenario: Question: "Lot Size?" Answered by "X" under "Small"(A3) "Medium"(A4) or "Large"(A5) If (A3) = "X", then (J2)= 500 If (A4) = "X", then (J2) = 600 If (A5) = "X", then (J2) = 700 Obviously, these function will have to recide in the J2 box. I can get it to work individually using the "=IF" function, but how can I insert multiple "=IF" functions into the (J2) box? I have tried using the "=OR"...

adjust cell height
Is there a formula to adjust cell heights in a cell so there's a certain amount of pixels above and below the letters/numbers? This is so the numbers aren't so crowded in the cell if formatted "text-wrapped"? thanks. You can auto fit the row height. There is no option for pixels above and below similar to Word where you can format the text to have leading and trailing space. -- HTH... Jim Thomlinson "johncaleb" wrote: > Is there a formula to adjust cell heights in a cell so there's a certain > amount of pixels above and below...

Formula to copy cell in Excel from on file to another
Hi, I have 19 excel files in a folder and I have created another excel file (B) in which I am trying to set a formula that can look in Cell "A6" which has the path with the file name of the first file (1) then copy From the first File (1) cell "L25" an place it in Cell "Y6" of File (B). I want the file names or file names with the path to be in file (B) so that I can change the name easily instead of changing all formulas. Hope I have clear explanation! Thanks, The function you'd want to use is =indirect(). But =indirect() won't work ...

can't type "HSA" into a cell
I have a part name HSA but can't type it into any cell, because Excel will change it to HAS automatically? Please help. Thanks, Excel is trying to "correct" your spelling. To avoid: Tools > Options... > Spelling > Autocorrect Options > Autocorrect and fix the dictionary -- Gary''s Student - gsnu200909 "ongcong" wrote: > I have a part name HSA but can't type it into any cell, because Excel will > change it to HAS automatically? Please help. > Thanks, > ...

Need fast way to move from cell to home
What is the quickest way to move from a cell to home (A1) in Excel? I thought you could click on something or hold down two keys in order to accomplish this, just can't remember. Thanks. CTRL+HOME -- Kind Regards, Niek Otten Microsoft MVP - Excel "Debbie" <Debbie@discussions.microsoft.com> wrote in message news:B5A4892A-F180-47FA-A9F0-F91BA3CAFA64@microsoft.com... > What is the quickest way to move from a cell to home (A1) in Excel? I > thought you could click on something or hold down two keys in order to > accomplish this, just can't remember. T...

How can I only the cells showing by a filter?
If I select to copy a filtered list of cells and pasted to another app, only the visible cell contents got copied. If I pasted to another excel sheet, all hidden cells got copied as well. How can I only copy filtered cells within Excel? Thanks! Excel should paste just the filtered data between sheets. I don't know why it doesn't for you. Which version of Excel are you running? I tested on 2003 version. A workaround would be....................... F5>Special>Visible cells only>OK Copy and paste. Gord Dibben MS Excel MVP On Wed, 19 Dec 2007 13:54:03 -0800, Pin...

Data range reference in a cell
Hi I'm working with a large number of data ranges and i want to be able to change the source data of one chart by typing the range name in a cell rather than directly into the chart source tab. For example: in B1 i've typed "SCK" (which is defined as a range on another sheet) I want to be able to change B1 to read "JAB" and the chart to change it's source data to the JAB range which is also defined. Thanks ...

Auto-checking a checkbox based on cell contents.
Is it possible to set or "check" a checkbox in real time, based on the contents of a cell? I know the cell link field can tie a cell to a check box, but (if I understand it right) it's a one way link (meaning the cell reacts to the check box). Any help would be appreciated. Thanks, Ramon Hi Ramon this would require VBA using an event procedure like worksheet_change -- Regards Frank Kabel Frankfurt, Germany "Ramon Cantu Jr." <rcantu01@excite.com> schrieb im Newsbeitrag news:ff0472fd.0405261347.2244a140@posting.google.com... > Is it possible to set or &quo...

Number of characters per cell
A friend sent me a file that she swears was created and modified only in Excel 2000. However, there is one cell with a lot of text in it that appears to be showing only the first 255 characters (I didn't count, just guessed at the number). I believe that although Excel 97 allowed only 255 characters per cell, Excel 2000 allows up to 65,000 characters. The cell is formatted General, left aligned, wrapped, vertically centered and the row height is at the maximum of 407 - but visually there is room for more text in the row/cell (above and below the vertically centered text), but the text does...

How specify number format of cell value in concatenate function?
When combining text and cell values in a concatenate function, I would like to control the number format in the concatenate cell: comma separator, decimal place, etc. ="this is text " & text(a1,"$000,000.00") & " due on: " & text(b1,"mm/dd/yyyy") R1dgeway wrote: > > When combining text and cell values in a concatenate function, I would like > to control the number format in the concatenate cell: comma separator, > decimal place, etc. -- Dave Peterson ...

Cell Names, References ?
I am attempting to re-work an existing worksheet that contains cells that have been "named". Does anyone know how to change or delete a name once entered into the "Name Box"? When I attempt to write over the names, I receive and error message regarding references. Thank you. -- tbryson ------------------------------------------------------------------------ tbryson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36246 View this thread: http://www.excelforum.com/showthread.php?threadid=560418 Use CTRL+F3 to bring up the Defined Name dia...

sum based on content of other cells
Cells A7:A49 have pull downs with 20 categories to pick from. Cells H7:H49 has the value in hours spent on that category. I would like to get the sum of hours based on each category, and put this value in another cell. Can someone please help me with this ? Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You want to use the Sumif function. >-----Original Message----- >Cells A7:A49 have pull downs with 20 categories to pick from. >Cells H7:H49 has th...

Cell formulas that expire after a month
I have a spreadsheet that I'm using to track progress over the period of a year. One sheet is raw data that is manually entered daily and one sheet is monthly statistics based on that data. In the statistics worksheet are cell formulas calculating statistics relating to each month based on the raw data. If I change the data in March, I don't want January's or February's statistics to be changed. Is there a way to do this? Thanks, PaulH On Mar 7, 11:52=A0am, PaulH <paul.h...@gmail.com> wrote: > I have a spreadsheet that I'm using to track progress over the perio...

Bad visbility off a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, i am new here. I have a question, when I click in a cell in an excelsheet then this is obscured. I mean the square of the cell is almost invisible. So if there is a lot of figures in this sheet you can not find back the cell where u was working. Has anyone a solution how to change in a better visibiliy? Thanks in advance. Thijs On 3/3/10 8:04 AM, Thijs@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Hi, i am new here. I have a question, when...

Font enlarges when cell is linked to email
When I enter an email, the font enlarges from 8 to 10 and underlines itself. I can live with the auto underline if I must, but I need to find a way to prevent the change of font size. Are there any remedies? Thanks for your help. Michael In the AutoCorrect>Autoformat as you type turn off "internet and network path etc." I have never found a way to prevent the hyperlink from defaulting to 10 point font. Gord Dibben MS Excel MVP On Fri, 12 Jun 2009 16:09:52 -0700 (PDT), Michael Lanier <michaelrlanier@gmail.com> wrote: >When I enter an email, the font enlarges fro...