# Thread: Formula for pulling data from 1 sheet copying to another and removing the blank cells. Thanks: 0 Likes: 0

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

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.

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

=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))),"")

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

worked like a charm,

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

You're welcome & thanks for the feedback