 # 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

• 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

• Thanks Jindon! that code is working perfectly.

• 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!