Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
You have to be more specific, what are you trying to achieve? –  Sandeep Bansal Apr 12 '11 at 16:30
    
I want to select the value of a cell above an empty cell. –  Jeremy Boyd Apr 12 '11 at 16:31
add comment

3 Answers

up vote 0 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
    
I don't want to do it manually, and I was hoping it would be a function that I could then run inside another sheet. –  Jeremy Boyd Apr 12 '11 at 16:35
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.