I have excel workbook with 4 sheets in it. It is a bid system workbook where employees bid by seniority for shift, days off and vacation time. The Master Sheet has members broken down by Shift Supervisors, Team Leads and Specialists. I have drop down menus with employee names, Choice of shifts (1st, 2nd, 3rd), and days off (SM, MT,TW, WT, TF, FR, and SS)
I have the other 3 sheets in the workbook labeled 1st, 2nd and 3rd. Each sheet has 6 months with rows for each month, day and date. I want members name and days off to auto fill based on the shift they selected. Also if possible approved vacation dates.
EX.
Month June 1 2 3 4 5 6 7 8 9 10 11 12 13 14
1st Shift S S M T W T F S S M T W T F
Employee 1 X X V V V
I am requesting assistance with formula or formulas to make this work. Appreciate any advice or assistance.
I would like an X to fill in for each employee days off. Ex. An X under day S & S for each of the 6 months.
To clarify, you entered the date range exactly as shown, including the word "to" between the dates? The formula was written anticipating that the date would be entered as 3/4/2023-3/10/2023. I'll have to investigate...I think that's an easy fix. So you prefer the format mentioned in your last post...3-4-23 to 3-10-23?
Are the three shift worksheets supposed to be independently set for the initial baseline month? In other words, if you set the month-year baseline on one of the sheets--let's say January 2024 on the "1st" sheet--should the other two sheets also use that same baseline month-year...so you'd display monthly calendars for Jan-Jul 2024 on all three sheets?
Here is a version that consolidates some of the ideas in your workbook. We start by bringing together all of the lists that are used by Data Validation for forming drop-down lists or by the formula for convenience in referencing shift numbers, positions and days off:
The single (but multi-step) formula builds the output shown: labels for the month-year and shift number, day numbers and days of the month, sub-blocks for the different types of positions along with totals for those sub-blocks and X's and V's for denoting "weekends" and vacations. This formula spills the results for a single month. For now, the easiest approach is to simply copy this formula to the clipboard and paste it anywhere in column A (this is critical) where you would like the next month's table to begin. The formula "looks" up to determine how many monthly calendars have already been generated so that the subsequent month will be produced. Conditional formatting is then used to identify the heading rows (blue), summary total rows (gold), and end row (black). The three shift sheets rely on the month-year selected on sheet 1st (cells AJ2:AK2) for the initial baseline month.
I haven't performed extensive testing with it, but it seems to work fine for the scenarios I've tried. Please let me know if anything seems amiss.
Here is a link to the full version of the file...
To create this, once the 1st sheet was working correctly, I duplicated the 1st sheet and renamed the duplicate to 2nd, and then deleted the month-year selector on 2nd...and then pasted the formula from the first month on sheet 1st into the cell where I wanted the first month to appear on sheet 2nd. For sheet 3rd, I duplicated the 2nd sheet. Pasting the formula below the monthly blocks builds a new monthly output table. I considered using recursion via a LAMBDA function to automatically spill seven monthly blocks at once, but haven't had the time to work on that.
Here is a version that consolidates some of the ideas in your workbook. We start by bringing together all of the lists that are used by Data Validation for forming drop-down lists or by the formula for convenience in referencing shift numbers, positions and days off:
The single (but multi-step) formula builds the output shown: labels for the month-year and shift number, day numbers and days of the month, sub-blocks for the different types of positions along with totals for those sub-blocks and X's and V's for denoting "weekends" and vacations. This formula spills the results for a single month. For now, the easiest approach is to simply copy this formula to the clipboard and paste it anywhere in column A (this is critical) where you would like the next month's table to begin. The formula "looks" up to determine how many monthly calendars have already been generated so that the subsequent month will be produced. Conditional formatting is then used to identify the heading rows (blue), summary total rows (gold), and end row (black). The three shift sheets rely on the month-year selected on sheet 1st (cells AJ2:AK2) for the initial baseline month.
I haven't performed extensive testing with it, but it seems to work fine for the scenarios I've tried. Please let me know if anything seems amiss.
Here is a link to the full version of the file...
To create this, once the 1st sheet was working correctly, I duplicated the 1st sheet and renamed the duplicate to 2nd, and then deleted the month-year selector on 2nd...and then pasted the formula from the first month on sheet 1st into the cell where I wanted the first month to appear on sheet 2nd. For sheet 3rd, I duplicated the 2nd sheet. Pasting the formula below the monthly blocks builds a new monthly output table. I considered using recursion via a LAMBDA function to automatically spill seven monthly blocks at once, but haven't had the time to work on that.
Would it be possible to have each shift show 6 months of schedule (Jan to July) on the Monthly Staffing 1st, 2nd and 3rd. See Attached. Also the shift selections for Shift Supervisor needs to be 1st, 2nd, 3rd and Floater. All other staff shift selections need to be 1st, 2nd and 3rd. Everything seems to be working great. Thank you again for the help.
If you copy the formula from the upper left cell of any existing block (just the single cell containing the formula) and paste it a row or two below the lowest existing monthly block (in column A), a new block for the next month will be generated. You can do this for 6 months (Jan to Jun) or 7 months (Jan to Jul) or longer if desired.
This is the first I'm hearing about a "Floater" selection that depends upon the position being Shift Supervisor. Presumably you mean that if someone is shown as a Shift Supervisor on the Master table, then the drop-down options for their Shift need to be 1st, 2nd, 3rd, or Floater. Is this correct? If so, then let's suppose someone named John is designated as a Floater on the Master table. How would one know which tab (which worksheet named either 1st, 2nd, or 3rd) John should appear on? What rule is being applied to determine whether John is a floating shift supervisor for 1st shift, 2nd shift, or 3rd shift? More detail needed here...
Yes, I understood that...but my questions weren't asking about that. I am trying to understand where to place a Shift Supervisor who is designated as a Floater. Should they appear on 1st shift, 2nd shift, 3rd shift?...how would one know?. The formula needs to include some logic in it to make that determination. What is that rule? In my previous post, the hypothetical case is described where John is a Floater. When constructing the monthly tables for 1st shift, 2nd shift, and 3rd shift, should John appear on any of them? Which one(s) and why?...what determines when John's name appears on one of the Shift sheets?
Sorry, the floater needs to be able to appear on any of the shifts. The floating Shift Supervisor fills in when the other Shift Supervisors or Team Leads are on Vacaction.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
