Formula for pulling data from 1 sheet copying to another and removing the blank cells.

[blanx]

So I have a Sheet "Roster" in I8:I35 I have Names, not all cells are full, some days they are some they are not.

On a second sheet i want to have a formula that will copy only the Cells with Data in them and put them in B17:B???

Im having trouble figuring out what formula combinations to use.

 

[Fluff]

How about
=IFERROR(INDEX(Roster!$I$8:$I$35,AGGREGATE(15,6,(ROW(Roster!$I$8:$I$35)-ROW(Roster!$I$8)+1)/(Roster!$I$8:$I$35<>""),ROWS($A$1:A1))),"")

 

[blanx]

worked like a charm,

 

[Fluff]

You're welcome & thanks for the feedback