Formula for producing one list from another !

[Guest]

Hi all,

I have the table below, in a worksheet named " Attendance" which has 5
employees, Alf to Eric.
Each Person has a works No. 1-5
Column C shows whether they are in work or not (Y=In)

Works No Name In
1 Alf Y
2 Bob
3 Chris Y
4 Dave
5 Eric Y

I wish to extract the information from this table into a list (preferably
onto another worksheet) which removes those that are not in and shows just
those that are in.

I do not want rows that are blank. I want them shuffled up so that they are
in consecutive rows, see below

Works No Name
1 A
3 C
5 E

Can anyone help with the formula?

Chuckee

 

[Guest]

What are you doing with the data after you have it listed? Calculations?
Just looking at it, printing?

If you are not doing additional calculations with it why not use
'Data'...'Filter'....'autofilter' and use the drop down arrow to filter for
only the ones with 'Y'. If you needed it on another sheet you caould just
copy and paste.

 

[Guest]

Tim,
Thanks for your reply.
i am only looking at it and printing. I could use the
'Data'...'Filter'....'autofilter' option but i wondered if the process could
be 'automated'

chuckee