Clearing out spaces

[Simon]

I have a excel sheet that has a list of products in a column to which a user
can put a y in the next column to the right if they require that item i.e:
A B
1 Product1 y
2 Product2
3 Product3 y
4 Product4
On a neighbouring sheet I have a column in which each cell contains an IF
statement that says if the cell in the first sheet displays anything give
the Product name to the left. I end up with:
A
1 Product1
2
3 Product3


The problem I have is that that I need to convert this column with gaps in
into a new column, listing only the selected products without the gaps. It
needs to be automatically on document save or close. There is a good way of
doing this out there somewhere but I have no idea what it is. Please Help!

 

[Teethless mama]

=IF(ISERR(SMALL(IF(rngB="y",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(IF(rngB="y",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed