How to search cells that are blank and copy the last cell above

shegre · Aug 11, 2011

What formula can I use that searches through an array of cells and checks if the cell is empty or not. If the cell is not empty it returns the value in that cell. If the cell is empty it copies the cell above it that is not empty.

The first cell that is checked will never be empty.

T. Valko · Aug 11, 2011

shegre said:
What formula can I use that searches through an array of cells and checks if the cell is empty or not. If the cell is not empty it returns the value in that cell. If the cell is empty it copies the cell above it that is not empty.

The first cell that is checked will never be empty.

Where is this data range? Is it a single row or column? Where do you want the results to appear?

Datsmart · Aug 11, 2011

"Manual" method to copy values to a vertical range.
Select Range you want to Fill
On the Home tab, in the Editing group, click Find & Select, and then click Go To.
(Keyboard shortcut*for Go To is CTRL+G)
Click Special…
Blanks | OK
Hit the "=" key. Hit the Up arrow. Hold down Ctrl and hit Enter.
(you now have a formula in each blank cell)
Select Range again, not just blank cell range.
Crl-C to copy
In the Home tab, Click Paste Icon dropdown arrow
Click Paste Values
(you will now have the values in each former blank cell)

shegre · Aug 11, 2011

As the data can vary in size, I would like the formula to check all of Column C.

So single column, multiple rows.

The formula will be in a cell in a different sheet (same workbook)

Using Excel 2007 - forgot to mention

Datsmart · Aug 11, 2011

The steps I posted above will input a formula in all blank cells of the Selected range.
The steps also copy and paste "Values" into the cells. You can skip that part if desired. Then cells with existing data or formulas will not be affected.

shegre · Aug 11, 2011

Datsmart

The formula in the cell that was blank is =C10, which is the cell above it that has data, however, I don't want to do this manually. I am looking for a formula that checks a cell if it is empty or not and if it is, it copies the cell from above.

Datsmart · Aug 11, 2011

I am not sure what you really need.
Can you post a before and after sample of data and what you expect it to change to after a formula is added?

shegre · Aug 11, 2011

I am trying to create a report (report 1 below) in one worksheet that requires particular data from another report (report 2 below) in another worksheet (this report is copied from an auto-generated report from an application). I need a formula that will check 3 cells in report 2 (A1, B1, & D1) and copy the data from cell G1 into Report 1, C4 (as B4 in Report 1 = D1 in Report 2)

Report 1
<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=410><TBODY><TR><TD height=24 width="27%">

A

</TD><TD height=24 width="23%">B
</TD><TD height=24 width="13%">C
</TD><TD height=24 width="23%">

D

</TD><TD height=24 width="15%">E
</TD></TR></TBODY></TABLE>
<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=410><TBODY><TR><TD height=24 width="27%">

State:

</TD><TD height=24 width="23%">

Available

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">

Duration:

</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

0: No Reason

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

1: Finishing Call

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

2: Break

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">

34:53:02

</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="36%" colSpan=2>

3: Coordinating/Dispatching

</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="36%" colSpan=2>

4: Meeting/Con. Call

</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

5: Voicemail/Email

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

6: Project

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="36%" colSpan=2>

7: High Priority/Immed Term

</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR><TR><TD height=24 width="27%">
</TD><TD height=24 width="23%">

8: Peer Support

</TD><TD height=24 width="13%">
</TD><TD height=24 width="23%">
</TD><TD height=24 width="15%">
</TD></TR></TBODY></TABLE>

Report 2
<TABLE dir=ltr border=1 cellSpacing=2 borderColor=#000000 cellPadding=2 width=503><TBODY><TR><TD height=15 width="12%">

A

</TD><TD height=15 width="15%">

B