BYPASS blank cell

[xtrmhyper]

I HAVE A SHEET1 AND SHEET2


IN SHEET1

A
1
2 HEART
3 EDWIN
4
5 XTRM


IN SHEET2 I HAVE LINK FROM SHEET2
MY FORMULA IS:

A
1 =Sheet1!A1
2 =Sheet1!A2
3 =Sheet1!A3
4 =Sheet1!A4
5 =Sheet1!A5

ANSWER IS:

A
1 0
2 HEART
3 EDWIN
4 0
5 XTRM

I WANT TO MAKE HAPPEN IS TO BYPASS THE BLANK CELL

NEED TO HAPPEN IS HERE:

1 HEART
2 EDWIN
3 XTRM
4
5

TNX IN ADVANCE:}

GODSPEED!

 

[Jerry W. Lewis]

There may be more elegant solutions, but

=IF(ISNUMBER(SMALL(IF(LEN(Sheet1!$A$1:$A$5)>0,ROW(Sheet1!$A$1:$A$5)),ROW())),OFFSET(Sheet1!$A$1,SMALL(IF(LEN(Sheet1!$A$1:$A$5)>0,ROW(Sheet1!$A$1:$A$5)),ROW())-1,0),"")

would do this. Note that this formula must be array entered
(Ctrl-Shift-Enter)

Jerry