Copy from worksheet 1 to worksheet 2, 3 , 4 etc..

[qteekat]

Okay. I learned a thing or two but still not working for me.

Sheet 1 looks like this


Monday Tuesday Wednesday Etc...

John Smith off 4am 4am
Katie Jones 4am 4am 4am
Dawn Moran 4am 4am off

etc....


I would like sheet 2 (renamed "Monday") to list the names with "4am" under
the column Monday. And not list the names with "off" under the Monday column.


So "Monday" sheet should look someting like

Schedule

Katie Jones
Dawn Moran
etc...

 

[Max]

This easy formulas model might appeal to you

Your source table as posted is in Sheet1,
with text in B1 across: Monday, Tuesday
and corresponding data in row2 down

In another sheet
Let's say B1 will house the day of interest, eg: Monday (text label)
Put in A2:
=IF(OR(OFFSET(Sheet1!A:A,,MATCH(B$1,Sheet1!$1:$1,0)-1)={0,"off"}),"",ROW())
This is the criteria col which reads the input in B1, and flags rows which
are neither blank nor contains "off"

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data in Sheet1,
eg down to B100? Minimize/hide col A. Col B will auto-return the required
names dependent on the day input in B1, with all names neatly packed at the
top. Test it by changing the day in B1 to: Tuesday, it'll return the names
for Tuesday. And so forth. With this flexibility you can have it all easily
extracted in just one sheet. Success? celebrate it, hit the YES below

 

[qteekat]

Hi Max,

I've tried to use the formula you gave me on a new workbook and it's not
working. It's not listing any names at all. What am I doing wrong?

Clueless,
Kathy

 

[Max]

Perhaps this illustrative working sample will help you
see where you might have gone off-track in your trial:

http://cjoint.com/?bfwOknxKNg
AutoExtract Names Schedule by Day.xls

Take it away. Let me know here how it went for you.
Do press the YES button (like the one below) in my earlier response