Not certain what function is needed, perhaps multiple V-Lookup arguments?

[Victor_the_boozehound]

Hello,

I'm trying to build a manpower scheduler, ideally the manager would plug in names on the "Scheduler" worksheet and it would be annotated on the monthly schedule, in this case "Oct-17". I've manually filled in the first two days, but how can I automate the process of filling out the monthly schedule?

https://imgur.com/xejQGTO
This is the scheduler worksheet, the manager plugs in names within the white or blue blanks (conditional formatting), not all blanks must be filled.

https://imgur.com/ZIwjjv6
This is the monthly schedule "Oct-17". In the bottom left there's the chart which annotates for example early shift on a weekend would be a yellow box with "HE" inside of it. On October 1st the "scheduler" has Sgt Jones, LCpl Mitchell, and LCpl Neafsey on weekend early shift so on the "Oct-17" when you reference their row in the Oct 1st column HE is within the cell.

I know that I need to do conditional formatting for the background color and that part isn't an issue. The issue is how to get the shift abbreviation "HE", "E", etc. within the corresponding cell. Any help would be appreciated.

Victor

 

[istiasztalos]

Hi Victor,

One not so elegant bur working solution would be to check each shift one by one in a big formula, you have to paste these 5 continuously, for better readability I'll paste them in separate lines:

=IF(ISNUMBER(MATCH($C8,OFFSET(dayheader,MATCH(DATE(YEAR(currentmonth),MONTH(currentmonth),D$7),daylist,0),2,4,1),0)),"E","")
&IF(ISNUMBER(MATCH($C8,OFFSET(dayheader,MATCH(DATE(YEAR(currentmonth),MONTH(currentmonth),D$7),daylist,0),3,4,1),0)),"M","")
&IF(ISNUMBER(MATCH($C8,OFFSET(dayheader,MATCH(DATE(YEAR(currentmonth),MONTH(currentmonth),D$7),daylist,0),4,4,1),0)),"N","")
&IF(ISNUMBER(MATCH($C8,OFFSET(dayheader,MATCH(DATE(YEAR(currentmonth),MONTH(currentmonth),D$7),daylist,0),5,4,1),0)),"HE","")
&IF(ISNUMBER(MATCH($C8,OFFSET(dayheader,MATCH(DATE(YEAR(currentmonth),MONTH(currentmonth),D$7),daylist,0),6,4,1),0)),"HM","")

This goes in the top left corner of your table, D8

I used some named ranges you'll need to set these up or replace them with cell references in your workbook:
'dayheader', this is on scheduler sheet, header of the date column, A7 on your screenshot
'currentmonth' is the month title on your monthly sheet, big merged cell in row 4 and 5 in your sheet, this has to be a valid date ! (by that I mean a date recognized by Excel as a value not just text)
'daylist' is the list of dates on the scheduler sheet, starting at A8 downwards

Lastly, the formula assumes that there are always 4 rows of a single day on the scheduler sheet

 

[Victor_the_boozehound]

Hi Victor,

One not so elegant bur working solution would be to check each shift one by one in a big formula, you have to paste these 5 continuously, for better readability I'll paste them in separate lines:...

Good Sir you are a wizard, I stared at that problem for hours and couldn't even figure out how to approach the problem. I made the changes you mentioned, and while it didn't function fully as intended, it did function to a degree. I placed the formula in D8 as you mentioned then copy/paste to the rest of the cells, only the 2nd filled but its something!

I then added more names into the scheduler, it appears that your formula works for "Scheduler" columns F and G but not H-J. Guessing this is the offset?
Also how did you reference the table in the bottom left?

New scheduler: https://imgur.com/FyzMB2b
New monthly schedule: https://imgur.com/a52QZwd

Victor

 

[Victor_the_boozehound]

I'm sorry the problem was on my end. On the scheduler I had a few hidden rows which threw off the offset. Elegant or not that works, thank you!

 

[istiasztalos]

You're welcome, I'm happy that helped

btw that table on bottom left doesn't need to be referenced because we checked all 5 shifts one by one (at the end of each line you'll see the "E","M"... assignments when the person is found in that column)