How to define blank cell?

If condition does not meet, then nothing in cell, which I use "" to define 
blank cell, however, some plug-in for Excel cannot recognize "" as a blank 
cell.
Does anyone have any suggestions on another approach to define blank within 
formula?
Thanks in advance for any suggestions
Eric
0
Utf
4/30/2010 5:02:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1489 Views

Similar Articles

[PageSpeed] 16

I don't think there is some universal way short of 'Clear contents' - and I 
hope you don't consider it as an option :-)

Really, when you have a formula in cell, it never is empty - whatever the 
formula returns, there is the formula itself too!



Arvi Laanemets.


"Eric" <Eric@discussions.microsoft.com> kirjutas sõnumis news: 
D14B1F06-18E6-4DF0-B57C-5C3066F2F688@microsoft.com...
> If condition does not meet, then nothing in cell, which I use "" to define
> blank cell, however, some plug-in for Excel cannot recognize "" as a blank
> cell.
> Does anyone have any suggestions on another approach to define blank 
> within
> formula?
> Thanks in advance for any suggestions
> Eric 

0
Arvi
4/30/2010 5:12:41 AM
Within a formula, generally you can use one of 2 tests:
=""
or 
ISBLANK(A1)
ISBLANK() is not true unless there really is nothing in the cell, not even a 
formula returning "".
Another option, that will return 0 for either an empty cell or one with "" 
in it would be:
LEN(A1)=0


"Eric" wrote:

> If condition does not meet, then nothing in cell, which I use "" to define 
> blank cell, however, some plug-in for Excel cannot recognize "" as a blank 
> cell.
> Does anyone have any suggestions on another approach to define blank within 
> formula?
> Thanks in advance for any suggestions
> Eric
0
Utf
4/30/2010 5:14:01 AM
RE:  some plug-in for Excel cannot recognize "" as a blank
cell.

That's because the cell is NOT blank and contains a formula.

I prefer to have formulas return Zero over empty text as Zeros are numeric 
and can be easily hidden.

Perhaps you question should detail what you want to do?


-- 
Regards
Dave Hawley
www.ozgrid.com
"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:D14B1F06-18E6-4DF0-B57C-5C3066F2F688@microsoft.com...
> If condition does not meet, then nothing in cell, which I use "" to define
> blank cell, however, some plug-in for Excel cannot recognize "" as a blank
> cell.
> Does anyone have any suggestions on another approach to define blank 
> within
> formula?
> Thanks in advance for any suggestions
> Eric 

0
ozgrid
4/30/2010 6:56:57 AM
As long as the cell is going to have a formula, the cell will never be empty.

If I have to make sure that the blank looking cells are really empty, I'll use
this technique.

I'll use a formula like:
=if(a1>10,"too big",na())
And drag down the range

Then I'll clean up the #n/a's.  But this will lose the formulas from those
cells.

I'll select the range
Edit|goto (or F5 or ctrl-g)
Special|formulas|Check errors (uncheck the other choices)
hit ok
hit the delete key on the keyboard

Remember that this deletes the formulas in those cells!

Eric wrote:
> 
> If condition does not meet, then nothing in cell, which I use "" to define
> blank cell, however, some plug-in for Excel cannot recognize "" as a blank
> cell.
> Does anyone have any suggestions on another approach to define blank within
> formula?
> Thanks in advance for any suggestions
> Eric

-- 

Dave Peterson
0
Dave
4/30/2010 12:37:53 PM
Reply:

Similar Artilces:

How do I set a colour to 4 cells based on the value of a cell
I want to assign a colour to a group of 4 cells (ie a1 to a4) based on the colour that is typed into another cell (ie d1). It could be that I need the option of using 10 colours. eg if d1 was "green" then a1-a4 would be coloured green. Andy For greater than 3 colors(4 if use default no color) you will need VBA. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set rng1 = Intersect(Target, Range("D1")) If rng1 Is Nothing Then Exit Sub On Error GoTo endit Application.Enab...

Reference to cell with external spreadsheet
I have a lookup function with a very long range name which referrs to another spreadsheet. I would like to make a cell with the text of the range and just refer to the cell in the lookup function. Is this possible? Thank you On 19 Dec, 20:43, B Lane <B L...@discussions.microsoft.com> wrote: > I have a lookup function with a very long range name which referrs to ano= ther > spreadsheet. I would like to make a cell with the text of the range and j= ust > refer to the cell in the lookup function. Is this possible? > > Thank you If you long range name is in ...

blank cell turns to 0
Hi Guys, Using Excel 2000. I am trying to work on a sheet someone else started but can't finish. In a cell on the sheet that the formula below is pulling the data from there is a blank field but there is a 0 in the cell that has this formula in it on my new sheet. If the cell on the first sheet is blank, I want the cell that equals that cell on my second sheet to be blank too. ='SVMMC-admin, tx, blue cards'!$B$3 The sheet is a yes/no sheet and when there is a 1 in the cell it means yes, when there is a 0 in the cell it means no, when the cell is blank, it means Not Appl...

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Can't find cells to change content?
I have been given an Excel document I'd like to use as template. However, there is one item of text that I cannot change. In fact I can't seem to find which cell it is entered in. It appears as an overlay over the cells and does not relate to them. I can click on any other item and see to which cell it relates. Inserted Images highlight, and can be moved and placed anywhere. This text is not an image either as it does not give the image handles. If I select the whole document and delete the contents the text goes away. Any suggestions? NS <calypson@bell_kill_this_south.net&...

IF STATEMENT REFERENCING A BLANK CELL
I'm trying obtain a blank cell with an IF statement based on the formula B6-SUM(B9;B11;B13;B15;B17): 1. if B6 contains a reference which returns a blank if the referenced cell is blank and 2. if B6-SUM(B9;B11;B13;B15;B17) returns a 0. I've resolved item 2 this way: =IF(D6-SUM(D9;D11;D13;D15;D17)=0;"";D6-SUM (D9;D11;D13;D15;D17)) but everything I've tried for item 1 returns an error message. If anyone can help, thanks. Hi try =IF(OR(D6-SUM(D9;D11;D13;D15;D17)=0;D6="");"";D6-SUM(D9;D11;D13;D15;D17 )) Frank Maureen wrote: > I'm trying obta...

Deleting part of a cell
I have a large number of cells containing text (name of co. or individual, varying text string length) followed by a space and an 18 character string such as this "Shaving Centers </0016000000IMo40>" - I wold like to retain only the initial text string (ie. "Shaving Centers" - without the quotes of course) whether the space remains is immaterial. Problem is the </*> part is not constant, though I think it is of uniform length. I tried using the "IF(Right(..." function but had no success using the substitution for the numbers ie, </*>. If unclear ...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

I need a macro to find cut and paste data to new cell
I have data that I have exported to excel that I need to reformat to be able to create a pivot table to check for duplicate entries. On importing the data which is in the form of journal entries the memo line is stting above the journal numbers in column D. I need to find all of the comments and cut and paste them to column C. The comments are not all the same but do contain the same word "To" in the comment. The journals are not all the same size and so the comment line does not appear at regular intervals. Any help appreciated as I am very new to VBA ...

Blank Charts and Paste Special
Blank charts seem to be ignoring the settings in the paste-special dialog. Using Excel 2007. I want to create several x-y scatter plots from different data sets. There is some non-trivial formatting of the axis and legend and so on that I want to maintain and not have to do over each time. So, I made the graph once on one set of data. Then I made a copy. Then on the copy I deleted all the data series. There are a bunch of data series on each chart, so I didn't want to copy a chart with the data on it. Then I made a copy of the now blank chart. Then I did select-copy on the data for the...

Blank password
Hi I have installed office 2000 on a brand new xp pro xp1 machine. The problem is that password does not stick in outlook internet account. I have to re-enter the password every time I send/receive email. The relevant registry entries are as follows; Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Microsoft\Office\Outlook\OMI Account Manager\Accounts\00000001] "Account Name"="Operations" "POP3 Server"="192.168.1.55" "POP3 User Name"="Operations" "POP3 Skip Account"=dword:00000000 "POP3 Prompt for Pas...

Deleting blank rows
How can I quickly delete blank rows from a list I've imported to an Excel worksheet so that I can after order, filter the entire list? Joana Pretty quickly! Select a column which includes the blank rows. Click Edit/Go To/Special . . Blanks - and OK. Then Right click on your selection and click Delete . . . and select Entire Row. Or, you could sort on a column containing the blank rows and that would put them all together, for easy deletion. Andy. "Joana" <joana.fernandes@aceplus.pt> wrote in message news:0c6c01c37c53$74163c90$a401280a@phx.gbl... > How can I quickly...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

Splitting Cells
Im trying to enter two values into the same cell. I've seen it done, it has a red diagonal line seperating the two values. Thanks for any help. -- MFFC2005 ------------------------------------------------------------------------ MFFC2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25816 View this thread: http://www.excelforum.com/showthread.php?threadid=392130 First format the cell as Text. Then enter the two values separated by any character you desire. The cell will hold exactly what you typed. -- Gary's Student "MFFC2005" wro...

Blank column to be unique increment
Hi Everyone, I'm trying to write an update SQL to fill an Integer column with an incremental number. Example: My table has 500 rows, I want this new Integer column to be filled with number of 1 to 500. Is there a way to do this via an update SQL statement? Thanks in advance. Here is one example: CREATE TABLE Foo ( keycol INT NOT NULL PRIMARY KEY, datacol CHAR(1), seq_nbr INT); INSERT INTO Foo (keycol, datacol) VALUES (1, 'a'); INSERT INTO Foo (keycol, datacol) VALUES (8, 'b'); INSERT INTO Foo (keycol, datacol) VALUES (11, 'c'); INS...

How do I add a new blank record (row)
I have a table that consists of one row for each record. Each row contains columns for inputing data and columns containing formulas. I am trying to create a macro that inserts a new row (record) ABOVE the last row (record) so that the newest rows are at the top of the table. Would like the new record created by the macro to be blank except for the cells containing formulas. I am easily able to create many records below or above the current record. However, I do not wish to have many blank records above the latest record - only one blank record at a time when I press a button. My u...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

Simple "if word exist among theese cells, then true"
Im banging my head on the "find" functions. They give me a -value- when I need TRUE/FALSE for an IF function. It must be a much easier way to return TRUE or FALSE if a word exist within an area: Data: A1: Orange A2: Apple A3: Banana Function: 1: Is "*Apple*" in A1:A3? = TRUE! 2: Is "*Tomato*" in A1:A3? = FALSE! -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945 View this thread: http://www.excelforum.com/showthread.php?threadid=517716 =N...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

Adding a field to RM Blank Document
I need help adding the distribution reference from the RM Distributon Work table to the RM Blank Document. Thank you.. ...

Cell Address in a pivot table
I have a pivot table which gets updated every day. Last row in the pivot table is 'Grand Total' and the number of rows in the pivot table varies every day. I have another function which needs the count of number of rows in the pivot table. Since the starting cell remains the same, I am counting the number of rows in the pivot table = Row Number of 'Grand Total' - Starting Cell value. Now my question is .. how do I get the Row Number of cell 'Grand Total'? Thank you for your time. Regards, Reddy ------------------------------------------------ ~~ Message posted ...

Who do I get Publisher 2003 to open to a blank page?
I'd like to get Publisher 2003 to open to a blank page - no "start" page, no "new publication" dialog box on the left...just click the icon and a plain, blank page opens. silver <silver@discussions.microsoft.com> was very recently heard to utter: > I'd like to get Publisher 2003 to open to a blank page - no "start" > page, no "new publication" dialog box on the left...just click the > icon and a plain, blank page opens. Tools > Options > Uncheck "Use New Publication task pane at startup" > User Assistance &g...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...

two lines in a cell
I am trying to get multiple lines to display in a cell without merging . I have to display text such a 1. Hello my name i 2. Duncan LeBlan on two lines in a single cell. The cell width is long and currently I need to insert the appropriate spaces to have the second line of text move down. The issue arises when I print as the display in either Normal view or Print Preview shows that the text is on two lines, but it prints with parts of the second line on the first line Is there any command or function that can be used to allow me to get 2 or 3 lineds of text formatted on different lines ...