Please Help with this formula...Thanks

[Schedule Formula - Please Help - Thanks!]

Below is a schedule...the Top half has the hours in A5:A24, a full months
schedule from B$5:AC$24. Within this area lists the name of the person
working. The second half of this form lists each person and how many shifts
they are working within the week. These cells have a formula, example
=COUNTIF(C$5:C$24,$A27) answers true 1 and false 0. I need this answer to
change if true to reference the first columns hours instead. I've tried a
million possiblities this week...what am I missing? I've tried IF, VLOOKUP,
INDEX, TEXT, #VALUE #REF, LOOKUP, MATCH. I've even tried to switch it
around, in the value 1 box, SEARCH for the Name from C5:C24 to A5:A24. It's
not working...THANK YOU FOR YOUR HELP!!! Please advise. Karen

Hours Sunday Monday Tuesday Wed
8/30/2009 8/31/2009 9/1/2009 9/2/2009
7A Rondeau McNichols McNichols McNichols
7A Jenkes Casavant
7A-C Labonte Bergeron Chiarini DiSandro
7A-C DiSandro Labonte
8A Machado Chiarini Sousa Cairone
8A Pereira Pereira
9A Baughan Colombier Jenkes
9A Vaughan Lawson McKinnon
11A Sousa Baughan Baughan
11A Trainor Machado Houle
3P Ferri Ferri Ferri Beaudreau
3P Rondeau Howard Perry Machado
3P-C Beaudreau Beaudreau DiSandro
3P-C Snowling Snowling
4P Houle White Sousa Campbell
4P Machado Rondeau Morrison Pereira
11P Ferri Howard Ferri Beaudreau
11P White McKinnon Perry Howard
11P-C DiSandro DeCesare Bergeron Perry
11P-C Snowling DiSandro Snowling

Shift/Week
Baughan 0 1 1 1
Beaudreau 0 1 1 2
Bergeron 0 1 1 0
Cairone 0 0 0 1
Campbell 0 0 0 1
Carnes 0 0 0 0
Casavant 0 0 0 1

 

[Schedule Formula - Please Help - Thanks!]

Hi Dan,

I'm trying to give each person thier hours underneath the schedule on the
day instead of the count of the shift.

Example below - this is what I need it too look like.

8/31/09
Baughan 9AM
Beaudreau 3P-C

Thanks...K

Shift/Week
Baughan 0 1 1
Beaudreau 0 1 1 2

 

[Bob Phillips]

This will only get the first instance

=INDEX($A$5:$A$28,MATCH($A31,B$5:B$28,0))

--
__________________________________
HTH

Bob

"Schedule Formula - Please Help - Thanks!"

 

[Schedule Formula - Please Help - Thanks!]

Thank you Bob!

I tried it and it came up N/A. Any ideas?

Karen

 

[Schedule Formula - Please Help - Thanks!]

I tried it on a true statement and it worked!!! Thank you...
How would I leave the value of 0 if it's false?

Thanks again!!!
K

 

[Bob Phillips]

Try

=IF(ISNA(MATCH($A31,B$5:B$28,0)),0,INDEX($A$5:$A$28,MATCH($A31,B$5:B$28,0)))

--
__________________________________
HTH

Bob

"Schedule Formula - Please Help - Thanks!"

 

[Schedule Formula - Please Help - Thanks!]

IT WORKED!!! THANK YOU SO MUCH! YOUR AMAZING - YOU JUST MADE MY DAY!

KAREN

 

[Schedule Formula - Please Help - Thanks!]

Hi Bob,

If I have someone scheduled for a double shift on one day, the 1st set of
hours appears but the second doesn't. Is there a function to get 2 results
in the same box? Beaudreau - wed 9/2, 3pm & 11pm shifts. Maybe a rept or
should i double the formula? You are really good at this, thought I ask
versus take another 6 days trying to experiment. Thanks!!!

Karen