Auto filling calendar (Index\match function)

[sc204]

I had trouble with a descriptive title for this one, sorry. I have had good luck in this forum before and have learned that there is usually more than one way to do something in Excel.

My current problem. The attached sheet is part of a scheduling spread sheet that I use. I enter shifts into a calendar grid (upper left of sheet). Names on the left, days of the block on the top. Letters represent the shift that someone will work ie: D is 7 to 4, listed as 7 A Red in the calendar portion. The formula that I use in the calendar section is =INDEX($A$5:$AC$53,MATCH("D",L$5:L$53,0),1) for instance for the D shift on Sept 5th of this block. It looks in the area of my grid for "D" in the L column from rows 5 to 53 and inserts the name from the first column (the person working that shift).

My problem is that our schedule has become more complicated. Now I have a couple of different possibilities for each shift. For the 7 AM shift I have D for 7 to 4 and D3 for 7 to 7 a 12 hr shift. I have figured out how to make the calendar time label change depending on which letter is entered in the grid. See the times section for Aug 26 (the rest still need to be changed). What I can't figure out is how to get the employee to fill in wether I use D or D3. Any thoughts?

Thank you,
Stuart

[DonkeyOte]

Without getting into the detail of the model itself is there any reason you can't use D* as opposed to just D as the criteria in your MATCH ?

[sc204]

I feel foolish, I didn't try that. That almost works. The problem I am running into is that I use codes throught the grid for peoples requests. For instance am would mean that they need the am shifts off, pb11 means that they are working at our other facility at 11 that day. Now the calendar is picking up am as the A shift and pb11 as the P shift. I have too many different shifts. I will run out of alphabet letters if I try to make them all unique from the other codes that are placed in teh grid.

Any other thoughts to make it look for any of up to 3 different letters in the coulumn?

Thank you for taking the time to look at this,
Stuart

[DonkeyOte]

I only just noticed you'd revised your reply !

Editing a post won't generate notifications to those subscribed nor will it bump the thread so if the edit is late it's best to reply c/o a new post.

If I've understood... using the sample file and say BK51 which is looking for either of D/D3 and accounting for the fact that D* is not a valid approach given false positive potential... another route might be:

=INDEX($A$5:$A$53,LOOKUP(9.99E+307,MATCH({"D","D3"},$B$5:$B$53,0)))

does that help ?

[yvonnecat]

what was the outcome of this calendar? I am working on the same type of schedule but a little more simple.