# Imported Data Creates Blank Cells That Aren't Really Blank

Feb 21, 2006

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.

## SUM If X Cells Aren't Blank

Aug 21, 2007

How should I use the If formula in K3 if I want to sum (A3+D3+H3) only if at least one of the cells is not blank. If all of the three cells are blank, I need K3 to stay blank.

I came with the following that worked with only A3+D3 but when trying to include a third cell (h3) I get a "value" result.

=IF(ISBLANK(A3),IF(ISBLANK(D3),"",A3+D3),A3+D3)

## Count Of Dates Less Than Current &amp; Corresponding Cells Aren't Blank/Empty

Apr 19, 2008

I have a formula '= COUNTIF(A1:A5,"<"&TODAY())' that counts the number of expired dates in one column. I want to include a second condition to this formula that will check if another column of dates are blank then they should not be counted. So if for example a1:a2 have expired dates, and b1:b2 have both got dates then they should be included in the count.

## Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

## Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I can count the blank cells withiin a range using

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

## IF Statement To Leave Cell Blank If Multiple Cells Are All Blank?

Mar 12, 2014

I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.

## How To Populate Blank Cells With Sequence Until Non Blank Cell Is Encountered

Mar 22, 2014

I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.

What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).

This all needs to be done in Arial, 10pt, white.

## List - Filling In Each Blank Cells With Value Contained In First Non-blank Cell Above It

Feb 27, 2013

I have a list that looks something like this:

Column B

Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5

The range of cells in column B containing the items has a name "ColStreams"

I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.

## Replace #VALUE! Error For Blank Cells With Blank Cell

Nov 17, 2008

The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.

=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))

## Ignore Blank Cells And Truly Blank Cells In Named Range?

Jan 13, 2014

Ok so my named range looks like this:

[Code]....

However, I want to ignore the "" cells and the truly blank cells... However, I think all of them will have "" since I have this formula in all of the ones I'm putting in the range:

[Code]....

How do I go about getting these results into a named range so I can use it on validation since validation only seems to ignore truly blank cells and not the "" ones.

## Why After Paste Value The Blank Cells Are Like Not Blank Anymore

Aug 13, 2013

the situation is i have a sheet, contains like 500 cells, from A1, to T500, some of the cells are blank, then i figure that from cell A1 to E500 not all the words in capital, so i make it with upper on the2nd sheet, then, i copy + paste value back to the first sheet (A1 to E500)

i get wht i want, but why it's like there's no blank cells anymore on my worksheet.

like you know, if u want to jump from a filled cell to the blank cell, you would hit Ctrl + Down, it jump to the very end of the datas that i paste value before.

so when i hit Ctrl + Down from A1, it jumps to A500, meanwhile the filled cells only 10 cells.

## Copying Down Blank Cells Until Blank Row Is Reached?

May 24, 2014

I have a spreadsheet with data in 7 columns. Columns A-d have one line of data in them but the other 3 columns have more than one line.

How do I write a macro which will copy the first row of the first 4 columns down until I reach an empty row?

## Count Blank & Zero Cells Where Corresponding Column Not Blank

Jun 15, 2007

I was curious if it is possible to give a single cell multiple number formats based on what the number is in the cell. So for example if my number is bigger than 1000, I would like to use comas so that it looks like this 1,000. However, if it is less than 1000 I would like it to look more like this 999.00.

## Fill In Blank Cells Referencing Non-Blank Cells Above

Apr 17, 2008

I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.

## If / Then Functions Looking For Blank / Not Blank Cells

Dec 14, 2012

I'm am trying to combine columns B and C in such a way that the result will appear as B: C when C is populated and B alone when C is not populated. I've tried a variety of functions, and I'm unable to get the result I want. This is my most recent attempt:

=IF(C2=ISBLANK, B2, B2&": "&C2)

## Blank Cells Between Data In Same Row

May 24, 2012

I have a little problem here. I have my macro, and my save button in form5. I manage to keep the other data (from other form), but when I use this save button for Form5, the details save in the same active row, but saved it starting in Column O, which it is suppose to save start from Column H.

The result shows something like below.

A B C D
21 Vanessa 540414101422 14-Apr-54

E F G H
24-May-12 10333 0129458856

H I J K L

M N O P Q
Yes W G
R S
122 T

Here is my code:

Code:

Private Sub save_Click()
ActiveWorkbook.Sheets("PATIENT").Activate
Range("A2").Select

Call lastCell

ActiveCell.Offset(0, 7) = p.Value
ActiveCell.Offset(0, 8) = km.Value
ActiveCell.Offset(0, 9) = kp.Value
ActiveCell.Offset(0, 10) = KKEP.Value
ActiveCell.Offset(0, 11) = KKO.Value
End Sub

Here is my macro:

Code:

Sub lastCell()
Selection.Copy
Sheets("PATIENT").Cells(Rows.Count, "H").End(xlUp).Offset(1).Select 'PasteSpecial 'xlPasteValues
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

## If Two Cells Are Blank Return Blank - If One Has Date Subtract Today Date

Apr 24, 2013

Using one spreadsheet with three date columns and two columns counting days.

If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval

Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)

If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval

Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4

## Copy Data In Non-blank Cells Within Range And Paste Into Cells On Another Worksheet

Jan 19, 2012

I have data in some of the cells within range A26:A39

These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?

I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.

I anticipate that there will be 4 non blank cells within this range.

Ideally I would have data from the nonblank cells copied and pasted to cells
A40
A41
A42
A43

## Drawing A Blank On How To Sum Cells With Like Data

Feb 5, 2010

I have a worksheet with 27,000+ rows. Item numbers are listed in column J and quantities in column E. The rest of the data on the sheet is not needed. An item number may be listed multiple times. I'm trying to create a new sheet that lists each item number once with the sum of all the quantities associated with that number. The data is sorted so all "like" item numbers are listed in consecutive rows.

## Copying Data Down Into Blank Cells...

Jan 25, 2010

I have a table with over 12,000 rows in it. In one column I have activity and the next a name.

A B

Walk John
Run Harry
Sleep John
*blank* Harry
Eat Percy
*blank* John
*blank* Harry

So *blank is completey blank and that means Harry also put time to sleeping, and again John and Harry both put time to eating. How can I make the blank cells auto populate with the data from the entry above it?

## Show Sum Of Data Only If All Cells In Row Not Blank?

Jan 17, 2012

If i have =SUM(C8:J8) in K8 and the sum of the values is 0

I only want to show 0 so long as there is a value typed in at least 1 of those cells (the value typed in those cells is often 0 fyi).

If all the cells between C8:J8 are blank then i want K8 to show nothing.

## Extract Data From Non Blank Cells

Sep 11, 2008

I have a worksheet with an overview that is filled with blank cells and wells with 1 values (see example):

1234abcd1efgh1ijkl1mnop1

I'd like to extract only the cells which are filled with the value 1 and concatenate them into the matching text. The result are to be placed in one column in a second worksheet:

Resultabcd1efgh2ijkl3mnop4

## Placing Data Into Blank Cells

Jul 6, 2006

I know this is basic but I'm having a hard time here. I'm trying to insert certain data into a column of blank cells. I just need the fields to be on there once. As of right now it is pasting the first field multiple times.

Private Sub AA_Click()

If PS = True Then
Range("A61:A70").SpecialCells(xlCellTypeBlanks) = "Pull Stations"
On Error Goto 0
End If

If CS = True Then
Range("A61:A70").SpecialCells(xlCellTypeBlanks) = "C-F-A Switch"
On Error Goto 0
End If

## Count Blank Cells That Have Data Below Them

Nov 10, 2006

I am looking for a formula to count 2 blank cells (and return the value as 1 rather than two) but only when the cell preceding has data in it. eg in A11 there is data but in A12 & A13 it is empty = 1. PS If you are on the boards Jim I will email you an update when I get home tonight.

## Data Validation List With Only Non-blank Cells

Aug 21, 2009

I have a list in one worksheet which comes from "=SALESMEN!\$D:\$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?

Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.

## Skip Blank Cells In Chart Data

Jan 29, 2011

I have a column of data that I want to display as a chart. However, there are some blank cells in the column. When I use a simple line chart, the chart drops the line all the way down to zero for the blank cells. If the blank cell is B4 in column B, is it possible to make excel ignore that cell and connect B3 and B5 with a straight line?

## Formula To Display Data When Some Cells Are Blank

May 6, 2013

I have attached a spreadsheet that is causing me difficulty. I currently have a formula that is displaying in V3 the highest grade when it looks up the data in A3,H3 & O3. Then this is repeated for W3 when the data is looked up in B3, I3 & P3 etc etc... BUT

I need the formula to work if only block one is complete i.e. (1 Explore grade, 1 Plan Grade, 1 Make Grade etc).(please see the example to understand what is meant by a block)

The current formulae will only display a grade if all cells are complete i.e., A3,H3 & O3.

So I am looking for the formula to:

If A3 has a grade in it I wish V3 to display it because its the only grade. (even if H3 & O3 are blank)

As and when H3 has a grade filled in I want the formula to select the highest and display it in V3 (again even if O3 is blank)

As and when A3, H3 & O3 has a grade in it I wish the formula to lookup and display the highest in V3

Ans this repeated for all different areas, Explore, Plan, Make etc.

example doc with formula.xlsx

## Leave Other Cells Blank If No Data In Cell

May 9, 2009

I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)

The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.

If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.

I think it has to do with using an IF statement followed with ""? Am I on the right track?

Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?

I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.

## Leave Other Cells Blank If No Data In Cell

May 9, 2009

I have 2 similar question.

I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)

The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.

If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.

I think it has to do with using an IF statement followed with ""? Am I on the right track?

Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?

I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.

## Cells Left Blank When Using Data Validation

Mar 4, 2003

I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.