Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A staying on schedule formula: | Excel Worksheet Functions | |||
Loan Amortization Schedule Formula | New Users to Excel | |||
Vesting Schedule Formula | Excel Discussion (Misc queries) | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) |