Schedule Formula - Please Help - Thanks
 

Hello Bob or anyone else that can help figure this out...thanks!!

If I have someone scheduled for a double shift on one day, the 1st set of
hours appear 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


"Bob Phillips" wrote:

Try

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

--
__________________________________
HTH

Bob

"Schedule Formula - Please Help - Thanks!"
soft.com wrote in message
...
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


"Schedule Formula - Please Help - Thanks!" wrote:

Thank you Bob!

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

Karen



"Bob Phillips" wrote:

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!"
soft.com wrote in
message
...
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



"Dan DeHaven" wrote:


Can you be a bit more specific about what the new formula is
supposed to
do. Is it supposed to count the number of "what" based on the time
value
in the first column? Give an example based on the data below.

Dan


Schedule Formula - Please Help - Thanks!;488581 Wrote:
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
 

It seems you have a list of names (eg: Beaudreau, Jean, etc) listed in
B5:B28, with corresponding info listed in col A next to the names. This
formulas play will extract multiple col A info (for names duplicated within
B5:B28, if any) and display the multiple results horizontally aligned with
the names that you list in A31 down as lookup values.

Here's the play:
List all the unique names (eg: Beaudreau, Jean, etc) in D4 across to say Z4,
in any order

Put in D5: =IF($B5="","",IF($B5=D$4,ROWS($1:1),""))
Copy across/fill down to Z28

You have the unique names to be looked up
listed in A31 down, eg: Beaudreau, Jean, etc

Place this in B31:
=IF(ISERROR(SMALL(OFFSET($C$5:$C$28,,MATCH($A31,$D $4:$Z$4,0)),COLUMNS($A:A))),"",INDEX($A$5:$A$28,SM ALL(OFFSET($C$5:$C$28,,MATCH($A31,$D$4:$Z$4,0)),CO LUMNS($A:A))))

Copy B31 across by say, 5 cols? to cover the max expected repeats per any
unique name (within B5:B28), then fill down as far as required. The multiple
results for any duplicated names will appear horizontally aligned, neatly
bunched to the left. Any good? Hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Schedule Formula - Please Help - Thanks!" wrote:
If I have someone scheduled for a double shift on one day, the 1st set of
hours appear 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