Don't know what to do...Plz HELP

[Please Help]

HI to all who reads this!

I have created a schedule for my employees at work. There are 3
sheets representing different departments (here, far, and back) with
multiple employees. I have them set up for a 7 day work week. I also
have 7 additional sheets called Mon, Tues, Wed, etc...

My problem is that I have Mon-Sun sheets that I need to have look at
each day of each of my schedules and list the names of the people that
are working for that day.

For example:
Mondays sheet should look at sheets "here" "far" and "back" I want it
to only look on Monday column of those sheets and if it sees that
someone is working that day it should put the names of the people
working that day in the list on Mondays sheet.

This is the "Here" Department :

Mon. Tues. Wed. Thurs. Fri. Sat.
Sun.

Tonya 330In

Frank 330In 330In

Phyllis 10

Dug 330In

Dylan 330In 330

Honor 330 10In


Mondays Sheet should show:

Here Department: Frank, Honor
Far Department: .......
Back Department: .......

Tuesdays Sheet should show:

Here Department: Phyllis
Far Department: .......
Back Department: .......

I have used the IF statement but can't seem to get it to work
correctly.


I have tried to do this for over 2 weeks and can't get it. Please
someone help me!

Thanks
Colby

 

[Max]

Maybe one possible approach ..

Assuming that in the dept sheets:
In col A are the staff names
In cols B to H are the Mon - Sun cols
And data is in row2 down

In the dept sheet: Here
---------------------------
Put in I2: =IF(ISBLANK(B2),"",ROW())
Copy across 7 cols to O2, then down as many rows as you have data

This sets up the look-up table which will be used in the weekday sheets
Repeat the steps to set-up the tables in "Far" and "Back"

In the weekday sheet: Mon
----------------------------------
Put in A1:C1 as col headers, the sheetnames: Here, Far and Back

Put in A2:

=IF(ISERROR(MATCH(SMALL(INDIRECT(A$1&"!I:I"),ROW(A1)),INDIRECT(A$1&"!I:I"),0
)),"",OFFSET(INDIRECT(A$1&"!$I$1"),MATCH(SMALL(INDIRECT(A$1&"!I:I"),ROW(A1))
,INDIRECT(A$1&"!I:I"),0)-1,-8))

Copy A2 across to C2,
then copy down as many rows as you have data in the dept sheets

This will extract the names of the staff working on Mon from each of the 3
dept sheets

Repeat the steps for the rest of the weekday sheets: Tues .... Sun
but with the col reference "I:I" in the formula for A2 changed to suit,
viz.:

In Tues: Change "I:I" to "J:J"
In Wed: Change "I:I" to "K:K"
In Thurs: Change "I:I" to "L:L"
In Fri: Change "I:I" to "M:M"
In Sat: Change "I:I" to "N:N"
In Sun: Change "I:I" to "O:O"

( You can use Edit > Replace [Find what | Replace with ]
to do the above changes easily )

If you'll like to have the sample workbook for the above
just post a "readable" email address in reply here

Hope the above helps!

 

[SusannaW]

Hi Colby

You can connect different Formulas via "&":

=IF(Sheet1!B2<>"";Sheet1!A2 & ", ";"") & IF(Sheet1!B3<>"";Sheet1!A3 &
", ";"") & IF(Sheet1!B4<>"";Sheet1!A4 & ", ";"")

Instead of "Sheet1" just enter the name of your Sheet and change the
Ranges... Then this formula should give the result, as you wish...!

Cheers

Susanna

 

[Please Help]

Max please send sample workbook. (e-mail address removed)

 

[Max]

Sample workbook on it's way, Colby !

Do drop a line or two here
on how it went for you ..

 

[Please Help]

Max...YOU ROCK...what you gave me works PERFECT! The only thing that
I forgot about my schedules is that there is an AM and a PM Mon - Sun.
Is there a simple way that I can get it to the same thing but look at
the text instead?

What I mean is that there would be AM and PM. And for the AM monday
to look at who has AM and put them on the AM sheet.

I hope that this is clear to you so if it seems confusing I appologize
in advance

Example if Honor had a 10 shift on Monday then MondayAM sheet would
list her but MondayPM sheet would not.

Sorry for this setback but your help is GREATLY appreciated!!!!

If this is too time consuming for you please let me know! I do not
want to bother you!

 

[Max]

Always glad to hear that it worked, Colby .. you're welcome !

Ok, onto your follow-on Q ...
a restructure of the inputs in cols B to H in the 3 dept sheets
to be *text* of the form: 10am, 330pm (instead of: 10In, 330In, etc)
would greatly simplify the possible solution
(since we could then just make use of SEARCH()
to check for the "am" and "pm" in the inputs)

With the above inputs change accommodated,
these amended constructs should work:

In each of the 3 dept sheets:

Put in I2: =IF(ISBLANK(B2),"",IF(ISERROR(SEARCH("am",B2)),"",ROW()))
Copy I2 across 7 cols to O2, then down as many rows as required (as before)
(this sets it up for extracts into the AM sheets)

Put in P2: =IF(ISBLANK(B2),"",IF(ISERROR(SEARCH("pm",B2)),"",ROW()))
Copy P2 across 7 cols to V2, then down as many rows as required (as before)
(this sets it up for extracts into the PM sheets)

In the sheet: AM-Mon
with the same structure as before (i.e. dept sheetnames in A1:C1)

Put in A2:

=IF(ISERROR(MATCH(SMALL(INDIRECT(A$1&"!I:I"),ROW(A1)),INDIRECT(A$1&"!I:I"),0
)),"",OFFSET(INDIRECT(A$1&"!$I$1"),MATCH(SMALL(INDIRECT(A$1&"!I:I"),ROW(A1))
,INDIRECT(A$1&"!I:I"),0)-1,-8))

(It's the same formula as previously given, since it's the same col "I:I"
referenced)

Copy A2 across to C2, then down as many rows as required (as before)

In the sheet: PM-Mon
with the identical structure: dept sheetnames in A1:C1

Put in A2:

=IF(ISERROR(MATCH(SMALL(INDIRECT(A$1&"!P"),ROW(A1)),INDIRECT(A$1&"!P"),0
)),"",OFFSET(INDIRECT(A$1&"!$I$1"),MATCH(SMALL(INDIRECT(A$1&"!P"),ROW(A1))
,INDIRECT(A$1&"!P"),0)-1,-8))

Copy A2 across to C2, then down as many rows as required (as before)

The formula in A2 for "PM-Mon" is identical in structure to that
in A2 for "AM-Mon" except for the change of "I:I" to "P",
since "P" is the entire col ref for capture of inputs with "pm" for Mon
in the 3 dept sheets that we set-up above

The rest of the formula set-ups in the rest of the weekday sheets
will simply follow similar lines, viz.:

For the "AM-Tues" .... "AM-Sun" sheets: Change "I:I" to "J:J" .... "O:O"
For the "PM-Tues" .... "PM-Sun" sheets: Change "P" to "Q:Q" .... "V:V"

Note: The "AM-Mon" to "AM-Sun" sheets above are in fact exactly the same
as the 7 weekday sheets (Mon, Tues ... Sun) that we set-up previously.
So you could re-use these sheets and just re-name them (add the prefix
"AM-")
(the descriptions given above are for completeness ..)

 

[Please Help]

Max,

I don't know if I can thank you enough!
If anyone needs help with something MAX is the 1 4 U!!!!

With some modifications to what you have given me I will be the
happiest person alive.....at least for a while

Thanks Again,
Colby

 

[Max]

Pleasure' Colby !
Thanks for the feedback

 

[Max]

Pleasure' Colby !
Thanks for the feedback