Excel formula needed

[FLdave12]

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.

 

[KRice]

Okay, so does this mean that one or more Shift Supervisors are not assigned a specific shift for this 6-7 month period, and instead they are considered floating for the entire 6-7 month period...to fill in on those days/shifts when there isn't at least one regular Shift Supervisor? How many Shift Supervisors are typically scheduled for any given day and shift?

How many members on the Master sheet would be designated as Floating? If more than one, how would you determine which one gets called to fill in when the need arises? Or is this something that the formula doesn't need to address, and instead you handle it on the fly when you discover that a Supervisor will be out?

 

[FLdave12]

We only have 1 floating Shift Supervisor. The other 3 are assigned to their shifts for 6 month bids. There only needs to be one Floating Shift Supervisor on the Master Sheet. All other positions, if Classification Specialists/Job Assignment/Reclass are either 1st, 2nd or 3rd.

Formula does not need to address call outs that is handled on the fly

 

[KRice]

Okay...thanks. That makes it easier. I'll update with the conditional drop-down so that anyone designated as a Shift Supervisor will have "Floating" as an option. Did you manage to extend the shift tables down to include additional months?

 

[FLdave12]

I have not. Since only have access to 365 at work have to wait until then. I will have to read your post on that to do so.

Thanks again for all the assistance.

 

[KRice]

Here are some minor adjustments to accommodate the Floating option for Shift Supervisors. Expand the number of lists on the Lists sheet to include two different lists (one for non-supervisors, the other for supervisors). The problem is that these lists cannot be directly called from Data Validation, due to some limitations with formulas/functions permitted by DV. So I recommend setting up a named variable called ddShiftList (short for drop-down list of shifts) in Excel's Name Manager and using a formula there to determine which of the two lists should be used, dependent on whether the member's position is a Shift Supervisor. Data Validation for Shift choices in the Master table then refer to this ddShiftList variable, which will return the appropriate list. This approach does have a vulnerability that you should be aware of. If a member is classified as a Shift Supervisor and then their Shift designation is chosen as Floating, and then their position is changed to a non-supervisor role, "Floating" will still remain in the Shift cell. If you then use the drop-down selector to re-choose their shift, you'll see that "Floating" is no longer an option. The point is that you could inadvertently have a non-supervisor as Floating through this somewhat convoluted sequence of steps.

MrExcel_FLdave12.xlsx
	A	B	C	D	E	F
1	Position	Member	Shift	Days Off	1st Approved Vacation	2nd Approved Vacation
2	Shift Supervisor	foxtrot	1st	MT	1/7/2024-1/16/2024	4/2/2024-4/15/2024
3	Shift Supervisor	delta	2nd	TF	3/2/2024-3/11/2024	6/6/24-7/7/24
4	Shift Supervisor	lima	Floating	WT
5	Shift Supervisor	mike	3rd	WT	3/11/2024-3/16/2024
6	Team Lead	charlie	2nd	FS	4/3/2024-4/6/2024	5/5/2024-5/10/2024
7	Team Lead	juliet	1st	SS	3/20/2024	4/7/2024
Master

Cells with Data Validation
Cell	Allow	Criteria
A2:A7	List	=Lists!$E$2#
C2:C18	List	=ddShiftList
D2:D18	List	=Lists!$F$2#

...and DDShiftList in the Name Manager refers to this formula:

=IF(tblMaster[@Position]="Shift Supervisor",Lists!$D$2#,Lists!$C$2#)

MrExcel_FLdave12.xlsx
	C	D
1	Shifts for Non-Supervisors	Shifts for Supervisors
2	1st	1st
3	2nd	2nd
4	3rd	3rd
5		Floating
Lists

Cell Formulas
Range	Formula
C2:C4	C2	=LET(a,SEQUENCE(3),a&CHOOSE(a,"st","nd","rd"))
D2:D5	D2	=VSTACK(C$2#,"Floating")
Dynamic array formulas.

These changes are also now reflected in the file whose link was posted previously.

 

[FLdave12]

Everything seems to be working well. I do have one other question. Are the rows for Total Job Assignment and Reclassification Specialists locked? I tried changing the fill color and it would not let me.

Thanks again for all your help

 

[KRice]

That's good to hear...glad it's working for you. About the "locked" question...no, nothing is locked. There were a few different approaches that could have been taken for this problem:
1. Prepare fixed calendar blocks with heading and summary total rows in fixed positions and pre-formatted (filled with the desired colors). Then in order to place the results in these shaded regions, the single formula presented would be broken into multiple parts and those formulas would be placed in the shaded regions to ensure the desire content was shaded as desired...or alternatively, the single formula could be modified to pad it with enough rows so that the header date rows fall where they should in cells of one color (blue), the members fall in the central bands of unshaded cells (white), and the totals fall in the other color (gold). But I don't have sufficient detail to know how large those various sub-blocks should be to reliably report out all of the members on a given month's calendar and for the results to consistently hit those fixed colored regions.
2. Use VBA--coding--to create the output and assign cell fill colors.
3. The approach I presented...Use a spilling formula, and since the monthly tables might be of different lengths depending on how many people are scheduled for that month, the target rows for fill colors change positions...so a different method is needed to apply the fill colors. I used Conditional Formatting to look for certain key words in columns A or B to determine the row type, and hence the fill color for that row. See Post #55 where you'll see CF mentioned below the worksheet snippet, along with the formulas used. You can change the row fill colors by going to Home > Conditional Formatting under the Styles submenu, then click on Edit Rules. In the Rule Editing subwindow, used the dropdown selector to show rules for the worksheet that you are on. Then click on whichever rule you'd like to change (either double click it or click once and choose Edit Rule). That opens a new subwindow where you can choose "Format..." where you can then change the fill color. I don't know of a good way to make these changes across multiple sheets, other than repeating these steps on each of the three shift worksheets. Be sure to apply your changes and click OK to exit...you should then see the new formatting appear.