Select the value of a cell above an empty cell

Question

How would I select a cell that is above an empty cell? I have a feeling it is going to be something super simple, but I haven't done any excel since 2004.

Here is a simple single column sheet.

data1
data2

data3
data4

data5

data6

I am hoping it would be a function and it should return data2, data4 and data4 in an array of sorts.

Answer 1

Jeremy - I think you would need to write a simple macro function to do this:

Public Sub FillBlanks()
    Set myRange = Application.InputBox( _
        prompt:="Select a range", Type:=8)
    Dim prevCellValue
    For Each c In myRange.Cells
        If c.Value <> "" Then _
            prevCellValue = c.Value _
        Else: c.Value = prevCellValue
    Next
End Sub

Note that this subroutine would only work on single columns or rows. Josh

Answer 2

Go to the blank cell where you want your result, and just type "=" and then hit the up arrow. OR type "=" then type the column letter followed by the row number. For example, if the cell you would like to display the result in cell B5, you would click on the B5 cell and type "=B4" (without the quotes) then press enter. This would give you the result of the cell B4 in the cell B5 that you have selected.

just go to where you want your result and type "=A2+A4+A6" This will add the results of the cells into the cell you have selected.

Answer 3

This formula, array entered with control+shift+enter

{=OFFSET(A1,(ISBLANK(A1:A9))*(ROW(A1:A9))-2,0)}

will return this array

={#REF!;#REF!;"data2";#REF!;#REF!;"data4";#REF!;"data5";#REF!}

Depending on what you want to do with it, you will need to ignore the REF errors and process the non-errors. It also may be easier than that. If, for instance, the "data" is numbers that you want to sum, say starting in A2

10
20

30
40

50

60

and you want to return 110 (20+40+50), then

{=SUM((ISBLANK(A2:A10)*(A1:A9)))}

That only works if you have a blank cell above your data.