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]

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?

 

[FLdave12]

I did not have the to in it. The way you have it is fine. It just filled in under January month with the V's

 

[KRice]

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?

 

[FLdave12]

Yes

 

[KRice]

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:

MrExcel_20231122.xlsx
	A	B	C	D	E
1	Year	Month	Shift	Position	Days Off
2	2024	January	1st	Shift Supervisor	SM
3	2025	February	2nd	Team Lead	MT
4	2026	March	3rd	Classification Specialist	TW
5	2027	April		Job Assignment Specialist	WT
6	2028	May		Reclassification Specialist	TF
7	2029	June			FS
8	2030	July			SS
9	2031	August
10	2032	September
11	2033	October
12		November
13		December
Lists

Cell Formulas
Range	Formula
A2:A11	A2	=SEQUENCE(10,1,2024)
B2:B13	B2	=TEXT(DATE(2024,SEQUENCE(12),1),"mmmm")
C2:C4	C2	=LET(a,SEQUENCE(3),a&CHOOSE(a,"st","nd","rd"))
D2:D6	D2	={"Shift Supervisor";"Team Lead";"Classification Specialist";"Job Assignment Specialist";"Reclassification Specialist"}
E2:E8	E2	={"SM";"MT";"TW";"WT";"TF";"FS";"SS"}
Dynamic array formulas.

The Master sheet is very much like the one posted earlier, so I won't include it here.
The sheet named 1st looks like this:

MrExcel_20231122.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH		AJ	AK
1	Monthly Staffing																																			Month	Year
2																																				January	2024
3
4	Jan-24	Members	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
5	1st Shift		M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W
6	Supervisor/Team Lead/Specialist Positions
7	Shift Supervisor	foxtrot	X	X						X	X						X	X						X	X						X	X
8	Shift Supervisor	mike			X	X						X	X						X	X						X	X						X
9	Team Lead	juliet						X	X						X	X						X	X						X	X
10	Classification Specialist	hotel					X	X						X	X						X	X						X	X
11
12	Total Supervisors/Team Leads		2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2
13	Total Classification Specialists		1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1
14	Total on Shift		3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3
15
16	Classification Administrative Positions	Members
17	Job Assignment Specialist	kilo	X	X						X	X						X	X						X	X						X	X
18	Reclassification Specialist	echo				X	X						X	X						X	X						X	X
19
20	Total Job Assignment Specialists		0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1
21	Total Reclassification Specialists		1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1
22	Total on Shift		1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2
23	end
24
1st

Cell Formulas
Range	Formula
A4:AG23	A4	=LET(mobase,DATE('1st'!$AK$2,MONTH('1st'!$AJ$2&1),1), sht,TEXTAFTER(CELL("filename",$A$1),"]"), pcat,Lists!$D$2#, dol,Lists!$E$2#, n,COUNTIF(A$1:A3,sht & " Shift"), mo,EDATE(mobase,n), da,SEQUENCE(1,DAY(EOMONTH(mo,0)),mo), dow,CHOOSE(WEEKDAY(da),"S","M","T","W","T","F","S"), rone,HSTACK(TEXT(mo,"mmm-yy"),"Members",TEXT(da,"d")), blnk,MAKEARRAY(1,COLUMNS(rone),LAMBDA(r,c,"")), end,HSTACK("end",DROP(blnk,,1)), rtwo,HSTACK(sht & " Shift","",dow), rthreea,HSTACK("Supervisor/Team Lead/Specialist Positions",DROP(blnk,,1)), rthreeb,HSTACK("All Positions",DROP(blnk,,1)), rlow,HSTACK("Classification Administrative Positions","Members",DROP(blnk,,2)), ary,FILTER(tblMaster,tblMaster[Shift]=sht,""), pm,CHOOSECOLS(ary,1,2), v1st,CHOOSECOLS(ary,5), v2nd,CHOOSECOLS(ary,6), v1b,IFERROR(TEXTBEFORE(v1st,"-")+0,""), v1e,IFERROR(TEXTAFTER(v1st,"-")+0,""), v2b,IFERROR(TEXTBEFORE(v2nd,"-")+0,""), v2e,IFERROR(TEXTAFTER(v2nd,"-")+0,""), doi,MATCH(CHOOSECOLS(ary,4),dol,0), vdo,IF(((MOD(doi-1,7)+1=WEEKDAY(da))+(MOD(doi,7)+1=WEEKDAY(da))>0),"X",IF(((da>=v1b)*(da<=v1e)+(da>=v2b)*(da<=v2e)>0),"V", "")), res,HSTACK(pm,vdo), ressrt,SORTBY(res,MATCH(TAKE(res,,1),pcat,0),1,CHOOSECOLS(res,2),1), pos,TAKE(ressrt,,1), restop,IF(sht="1st",FILTER(ressrt,((pos=INDEX(pcat,1))+(pos=INDEX(pcat,2))+(pos=INDEX(pcat,3)))>0),ressrt), resbot,FILTER(ressrt,((pos=INDEX(pcat,4))+(pos=INDEX(pcat,5)))>0), t_r1,HSTACK("Total Supervisors/Team Leads","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM(((p=INDEX(pcat,1))+(p=INDEX(pcat,2))>0)*(c="")))))), t_r2,HSTACK("Total Classification Specialists","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM((p=INDEX(pcat,3))*(c="")))))), t_r3,HSTACK("Total on Shift","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM(--(c="")))))), b_r1,HSTACK("Total Job Assignment Specialists","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM((p=INDEX(pcat,4))*(c="")))))), b_r2,HSTACK("Total Reclassification Specialists","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM((p=INDEX(pcat,5))*(c="")))))), b_r3,HSTACK("Total on Shift","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM(--(c="")))))), fintbl,IF(sht="1st",VSTACK(rone,rtwo,rthreea,restop,blnk,t_r1,t_r2,t_r3,blnk,rlow,resbot,blnk,b_r1,b_r2,b_r3,end),VSTACK(rone,rtwo,rthreeb,restop,blnk,t_r1,t_r2,t_r3,end)), fintbl)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:AG200	Expression	=$B4="Members"	text	NO
A4:AG200	Expression	=OR($A4="1st Shift",$A4="2nd Shift",$A4="3rd Shift",$A4="Supervisor/Team Lead/Specialist Positions")	text	NO
A4:AG200	Expression	=OR($A4="Total Supervisors/Team Leads",$A4="Total Classification Specialists",$A4="Total on Shift",$A4="Total Job Assignment Specialists",$A4="Total Reclassification Specialists")	text	NO
A4:AG200	Expression	=$A4="end"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AJ2	List	=Lists!$B$2#
AK2	List	=Lists!$A$2#

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...

Dropbox

www.dropbox.com

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.

 

[FLdave12]

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:

MrExcel_20231122.xlsx
	A	B	C	D	E
1	Year	Month	Shift	Position	Days Off
2	2024	January	1st	Shift Supervisor	SM
3	2025	February	2nd	Team Lead	MT
4	2026	March	3rd	Classification Specialist	TW
5	2027	April		Job Assignment Specialist	WT
6	2028	May		Reclassification Specialist	TF
7	2029	June			FS
8	2030	July			SS
9	2031	August
10	2032	September
11	2033	October
12		November
13		December
Lists

Cell Formulas
Range	Formula
A2:A11	A2	=SEQUENCE(10,1,2024)
B2:B13	B2	=TEXT(DATE(2024,SEQUENCE(12),1),"mmmm")
C2:C4	C2	=LET(a,SEQUENCE(3),a&CHOOSE(a,"st","nd","rd"))
D2:D6	D2	={"Shift Supervisor";"Team Lead";"Classification Specialist";"Job Assignment Specialist";"Reclassification Specialist"}
E2:E8	E2	={"SM";"MT";"TW";"WT";"TF";"FS";"SS"}
Dynamic array formulas.

The Master sheet is very much like the one posted earlier, so I won't include it here.
The sheet named 1st looks like this:

MrExcel_20231122.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH		AJ	AK
1	Monthly Staffing																																			Month	Year
2																																				January	2024
3
4	Jan-24	Members	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
5	1st Shift		M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W	T	F	S	S	M	T	W
6	Supervisor/Team Lead/Specialist Positions
7	Shift Supervisor	foxtrot	X	X						X	X						X	X						X	X						X	X
8	Shift Supervisor	mike			X	X						X	X						X	X						X	X						X
9	Team Lead	juliet						X	X						X	X						X	X						X	X
10	Classification Specialist	hotel					X	X						X	X						X	X						X	X
11
12	Total Supervisors/Team Leads		2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2	2	3	2	2	2	2	2
13	Total Classification Specialists		1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1
14	Total on Shift		3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3	3	3	2	3	3	3	3
15
16	Classification Administrative Positions	Members
17	Job Assignment Specialist	kilo	X	X						X	X						X	X						X	X						X	X
18	Reclassification Specialist	echo				X	X						X	X						X	X						X	X
19
20	Total Job Assignment Specialists		0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1
21	Total Reclassification Specialists		1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	1
22	Total on Shift		1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	2
23	end
24
1st

Cell Formulas
Range	Formula
A4:AG23	A4	=LET(mobase,DATE('1st'!$AK$2,MONTH('1st'!$AJ$2&1),1), sht,TEXTAFTER(CELL("filename",$A$1),"]"), pcat,Lists!$D$2#, dol,Lists!$E$2#, n,COUNTIF(A$1:A3,sht & " Shift"), mo,EDATE(mobase,n), da,SEQUENCE(1,DAY(EOMONTH(mo,0)),mo), dow,CHOOSE(WEEKDAY(da),"S","M","T","W","T","F","S"), rone,HSTACK(TEXT(mo,"mmm-yy"),"Members",TEXT(da,"d")), blnk,MAKEARRAY(1,COLUMNS(rone),LAMBDA(r,c,"")), end,HSTACK("end",DROP(blnk,,1)), rtwo,HSTACK(sht & " Shift","",dow), rthreea,HSTACK("Supervisor/Team Lead/Specialist Positions",DROP(blnk,,1)), rthreeb,HSTACK("All Positions",DROP(blnk,,1)), rlow,HSTACK("Classification Administrative Positions","Members",DROP(blnk,,2)), ary,FILTER(tblMaster,tblMaster[Shift]=sht,""), pm,CHOOSECOLS(ary,1,2), v1st,CHOOSECOLS(ary,5), v2nd,CHOOSECOLS(ary,6), v1b,IFERROR(TEXTBEFORE(v1st,"-")+0,""), v1e,IFERROR(TEXTAFTER(v1st,"-")+0,""), v2b,IFERROR(TEXTBEFORE(v2nd,"-")+0,""), v2e,IFERROR(TEXTAFTER(v2nd,"-")+0,""), doi,MATCH(CHOOSECOLS(ary,4),dol,0), vdo,IF(((MOD(doi-1,7)+1=WEEKDAY(da))+(MOD(doi,7)+1=WEEKDAY(da))>0),"X",IF(((da>=v1b)*(da<=v1e)+(da>=v2b)*(da<=v2e)>0),"V", "")), res,HSTACK(pm,vdo), ressrt,SORTBY(res,MATCH(TAKE(res,,1),pcat,0),1,CHOOSECOLS(res,2),1), pos,TAKE(ressrt,,1), restop,IF(sht="1st",FILTER(ressrt,((pos=INDEX(pcat,1))+(pos=INDEX(pcat,2))+(pos=INDEX(pcat,3)))>0),ressrt), resbot,FILTER(ressrt,((pos=INDEX(pcat,4))+(pos=INDEX(pcat,5)))>0), t_r1,HSTACK("Total Supervisors/Team Leads","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM(((p=INDEX(pcat,1))+(p=INDEX(pcat,2))>0)*(c="")))))), t_r2,HSTACK("Total Classification Specialists","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM((p=INDEX(pcat,3))*(c="")))))), t_r3,HSTACK("Total on Shift","",BYCOL(DROP(restop,,2),LAMBDA(c,LET(p,CHOOSECOLS(restop,1),SUM(--(c="")))))), b_r1,HSTACK("Total Job Assignment Specialists","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM((p=INDEX(pcat,4))*(c="")))))), b_r2,HSTACK("Total Reclassification Specialists","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM((p=INDEX(pcat,5))*(c="")))))), b_r3,HSTACK("Total on Shift","",BYCOL(DROP(resbot,,2),LAMBDA(c,LET(p,CHOOSECOLS(resbot,1),SUM(--(c="")))))), fintbl,IF(sht="1st",VSTACK(rone,rtwo,rthreea,restop,blnk,t_r1,t_r2,t_r3,blnk,rlow,resbot,blnk,b_r1,b_r2,b_r3,end),VSTACK(rone,rtwo,rthreeb,restop,blnk,t_r1,t_r2,t_r3,end)), fintbl)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:AG200	Expression	=$B4="Members"	text	NO
A4:AG200	Expression	=OR($A4="1st Shift",$A4="2nd Shift",$A4="3rd Shift",$A4="Supervisor/Team Lead/Specialist Positions")	text	NO
A4:AG200	Expression	=OR($A4="Total Supervisors/Team Leads",$A4="Total Classification Specialists",$A4="Total on Shift",$A4="Total Job Assignment Specialists",$A4="Total Reclassification Specialists")	text	NO
A4:AG200	Expression	=$A4="end"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AJ2	List	=Lists!$B$2#
AK2	List	=Lists!$A$2#

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...

Dropbox

www.dropbox.com

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.

 

[KRice]

Would it be possible to have each shift show 6 months of schedule (Jan to July) on the Monthly Staffing 1st, 2nd and 3rd.

Yes, did you try the guidance I offered in my last post...summarized here?

Pasting the formula below the monthly blocks builds a new monthly output table.

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.

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.

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...

 

[FLdave12]

Floater position only applies to Shift Supervisors. Don't need for any other positions

 

[KRice]

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?

 

[FLdave12]

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.