Reference Above Cell if no value is entered in referenced cell

  • Hi!
    I am looking for an Excel expression which I can put in a cell in the second sheet of a workbook in order to reference a cell in the first worksheet. If there is no value entered for the referenced cell, then I would like the cell above returned, if that is blank, then the cell above that... and so on.
    For Example assume in worksheet one cells A1 and A5 have data and no data in any other cell. I would like these values returned,
    For cells referencing A1 return A1
    A2 return A1
    A3 return A1
    A4 return A1
    A5 return A5
    A6 return A5
    A7 return a5


    Thanks in advance.

  • Hi, mosejon


    I hope the formula below does good for you


    In cell A1 of the Sheet2


    =IF(Sheet1!A1<>"",Sheet1!A1,INDEX(Sheet1!A$1:A1,MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1))),1))


    Ctrl + Shift and Enter to get out from the formula bar.
    Then drag down as you want.


    Jindon

  • Jindon,
    I was wondering if you might be able to elaborate on the specific purposes of the functions from the formula you supplied earlier. Specifically this portion of it...
    INDEX(Sheet1!A$1:A1,MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1))),1))
    The reason I am asking, is that while the formula works for cells in the 'A' column 'Sheet1' and the 'A' column of 'Sheet2' I am having trouble using it while trying to implement it in practical use.
    Specifically, I would like to reference cell 'A7' in 'Sheet1' from cell 'J2' in 'Sheet2' then reference cell 'A8' from cell 'J6' in 'Sheet2' etc. So I will not be simply filling in the column with the same formula.
    Thanks in advance for everything.

  • morsejon


    MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1)))


    Since Sheet!A$1 and ROW(A$1 are fixed, they won't change as you go down the row, but A1s change, increase by 1 as you go down.
    Formula above returns the corresponding row number out of 1:1 (ROW(A$1:A1)) is equal to maximum row which contains any value within the range of A1 to A and current row on Sheet1.


    Therefore, if you need to modify above portion, you need to match dynamic part of range which is A1 part to the first refernce row address.
    In case of A7, the portion is like
    MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))
    then, in any cell on Sheet2
    =IF(Sheet1!A7<>"",Sheet1!A7,INDEX(Sheet1!A$1:A7,MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7))),1))


    Or, is this easier to understand?
    =IF(Sheet1!A7<>"",Sheet1!A7,INDIRECT("Sheet1!A"&MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))))
    Good luck


    Jindon

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!