I need some help with my spreadsheet. I was hoping to turn it into a visual roster where everyone can see when they are working or not. It also allows us to see any gaps in the roster and quickly fill in the gaps if someone is unavailable.
I made a 'Schedule' sheet for data input and a 'Visuals' sheet to display them. I've made a mini schedule data table for convenience (not too sure if I can remove it and have my 'Weeks' name variable in the 'Schedule' sheet). My only issue is that for some reason, workers who start at 11 PM and finish at 7 AM don't appear at all in my chart.
From my limited excel knowledge, I know it's due to the second IFERROR function showing up as false for the second half but not too sure how to adjust it to allow those working on the night shift to appear and not change the correct morning and afternoon shifts.
There's also a calendar that I was planning to make but the roster chart is a priority. Can anyone offer any advice/recommendations for my issue? Thanks in advance!
Daniel
Hi Daniel,
What do you mean by "chart"?
You said that: "workers who start at 11 PM and finish at 7 AM don't appear at all in my chart", but there is no chart in your file, please clarify.
Sorry for not being specific Catalin. In the 'Visuals' sheet of the file I've attached, there is a chart at the bottom in a form of a bar graph (below the calendar and schedule data table). The y-axis displays the time starting at 6 AM and the x-axis display the employee names. The cells are conditioned to fill with the matching employee colour, showing when they are working.
Ok, I guess you're referring to range C50:T102 as "the chart".
Are you trying to say that this formula used in the above range is not working as expected?
=REPT(E$50, AND(ROUND($D52,6) >= IFERROR(ROUND(INDEX(Weeks, MATCH(E$50,$D$29:$D$44, 0), 1, WeekNumber), 6), 0), ROUND($D52,6) <= IFERROR(ROUND(INDEX(Weeks, MATCH(E$50, $D$29:$D$44, 0), 2, WeekNumber), 6), 0)))
Yes that's right (should have just put the formula in the OP). Any ideas or recommendations on how to display the shifts?
