To pull Works #
=INDEX($A$2:$A$6,SMALL(IF($C$2:$C$6="Y",ROW($C$2:$ C$6)-MIN($C$2:$C$6)-1),ROW(1:1)))
array entered (ctrl + shift + enter)

Copy this formula al the way down until you see a N/a# or alternatively
enclose it it with an if (Iserror (Index...) to avoid n/a# etc

Just change $A$2:$a$6 piece of the formula to to B$2:B$6 to pull names etc



"Chuckee" wrote:

Hi all,

I have asked this before but did not get a suitable answer
Below is a crude "sample" of what i want to do.
Ultimately i will only be viewing or printing the outcome and just wanted to
know if it can be automated or not. I didnt want to use filter or autofilter
options under the data tab.

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