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.

 

[FLdave12]

Ken,

Forgot to mention is there a way to set the start date of the first month and end date of the last month by specific dates? If not that is fine. Our bid period is every 6 months. The start date is usually mid month, for example July 14 and end date of Jan 11.

Thanks again for all you help.

 

[KRice]

Okay...so you still need two cells for vacation date "choices"? But each of those cells might contain multiple, non-consecutive dates? I'll try to give this a look over the next day or two. I'll also have a look at the link to see why the single dates weren't working...I didn't have any issues with them before.

P.S. It's Kirk, not Ken.

 

[FLdave12]

Sorry for the wrong name, Kirk.

Also, did you see my other question concerning start date of beginning month and ending date of last month? As I said, if not possible that is find. Is there a way to manually hide say
July 1 to 13 first month and last month stopping at say Jan 13. Just side note these values would not permanent for each bid period.

Really appreciate your help.

 

[KRice]

Sorry...I missed the other question. So to clarify, you'd like to specify a start and end date for the output calendars and then have results for those multiple months spill out into separate monthly calendars? And ideally, you would like to have results cleared for any dates in the start and end months that are not within the date range? I implemented a somewhat clunky way to accomplish some of this in the "1st" worksheet, where the calendar-generating formula is manually pasted below the calendar block above, but it doesn't clear results, and it doesn't automatically spill all relevant months. I'll look into it. The formula considers each month to be a spilled array consisting of multiple rows and columns. So nearly the entire formula would need to be looped through for each month and the final results for each loop (month) would then be aggregated to deliver all of the monthly results in one long spilled array. That may be doable with LAMBDA functions, and certainly with VBA.

 

[FLdave12]

Yes, and thanks

 

[FLdave12]

Kirk,

Yes, Just as you previously mentioned. I need two cells for vacation date "choices. But each of those cells might contain multiple, non-consecutive dates and block of time off as it is now, ie, 6/18/24-6/25/24. Also, I would like to specify a start and end date for the output calendars and then have results for those multiple months spill out into separate monthly calendars? And ideally, would like to have results cleared for any dates in the start and end months that are not within the date range.

Thanks for your help

 

[KRice]

Here is a modified version that adds the features you've described. In this version, you can enter a list of single dates or date ranges in either of the two vacation request blocks using commas to separate list items and hyphens to indicate a date range. Dates must be expressed in m/d/yy or mm/dd/yyyy formats.

Because there is interest in specifying a term (I believe you called it a bid period) for the beginning and end dates, those inputs are now done on the Master worksheet (cells J2 and J3 in this example). These inputs are used in the formulas and assigned to variables "cbeg" (calendar begins) and "cend" (calendar ends). And because you want to populate each of the Shift worksheets (1st, 2nd, and 3rd) with monthly calendars included in the bid period, I think it is appropriate to define a formula that works for a single month, and then repeatedly call that formula to deliver results for the other months. To do this, the original formula was taken as a baseline and then modified to adapt it for this purpose. This involved taking an entirely different approach for teasing out all of the vacation dates found within the members' input lists.

Briefly, the steps taken for building the vacation dates lists involved:

1. Merging the two user input cells (for 1st and 2nd vacation requests), and then separating those entries with a comma (see "vall").
2. Going row-by-row down the list of members and splitting apart "vall" to create an array that spills to the right, and each array element is either a blank, a single date, or a date range (see "vallsplit").
3. Examining each array element in "vallsplit" to determine if a hyphen is present. If so, the original array element is replaced with a sequence of dates covering the beginning and ending dates; and if not, the original entry is retained...and then joining together all dates within this array with comma delimiters (see "vd").
4. Taking the "vd" array row-by-row (which means all vacation dates associated for a given member), and combining those individual lists of dates into a single comprehensive list array with comma delimiters (see "vdcomb"...short for vacation dates combined).

Then we can apply logical tests to each date in the month ("da") to determine if it falls within the range described by "cbeg" and "cend" (if not, a blank), and if so, then does the date correspond to a regular day off (if so, an "X"), otherwise, does the date correspond to one found in that member's vacation date list (appropriate row of "vdcomb"), and if so, then return a "V", otherwise a blank is returned. These assignments are made in the "vdo" instruction.

These are the more significant changes in the formula that generates the monthly output. Most of the original formula and explanation (other than the revised portion described above) still applies. At this point, it makes sense to recast this base formula as a stand-alone function so that it can be called repeatedly by another formula that controls the number of times it should be called and then vertically stacks each of the resulting monthly calendars so that all months between "cbeg" and "cend" are displayed. The base function to generate a monthly calendar is named MonthCal and it requires six arguments that must be passed to it:

• cbeg - mentioned above...the beginning date for the term (bid period)
• cend - mentioned above...the end date for the term (bid period)
• pcat - the position categories (the array found at Lists!C2#)
• dol - the shorthand abbreviation list of days off (effectively, customized weekends)...(the array found at Lists!D2#)
• currshtcell - a cell found on the current worksheet ($A$1 used, a formality for getting the name of the worksheet)
• n - the index number of the month whose calendar should be displayed, a 0-basis index, so the "cbeg" month is 0

The easiest way to make this name assignment is to use Excel's Advanced Formula Environment (Excel Labs), where you can assign the name MonthCal, enter a description for the function (monthly schedule of vacation and days off), define the list of parameters that need to be passed to it (cbed, cend, pcat, dol, currshtcell, n), and then paste in the formula, beginning with =LET.

=LET(
    mobase, EOMONTH(cbeg, -1) + 1,
    mo, EDATE(mobase, n),
    da, SEQUENCE(1, DAY(EOMONTH(mo, 0)), mo),
    dow, CHOOSE(WEEKDAY(da), "S", "M", "T", "W", "T", "F", "S"),
    sht, TEXTAFTER(CELL("filename", currshtcell), "]"),
    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),
    doi, MATCH(CHOOSECOLS(ary, 4), dol, 0),
    vall, CHOOSECOLS(ary, 5) & "," & CHOOSECOLS(ary, 6),
    vallsplit, IFERROR(TRIM(DROP(REDUCE(0, vall, LAMBDA(a, b, VSTACK(a, TEXTSPLIT(b, ",")))), 1, 0)),""),
    vd, MAP(vallsplit,LAMBDA(d,LET(begin, TEXTBEFORE(d, "-", , , , d) + 0, end, TEXTAFTER(d, "-", , , , d) + 0, reqd, IFERROR(SEQUENCE(end - begin + 1, , begin), ""), TEXTJOIN(",", 1, reqd)))),
    vdcomb, BYROW(vd, LAMBDA(r, TEXTJOIN(",", 1, r))),
    vdo, IF( (da < cbeg) + (da > cend) > 0, "", IF( (MOD(doi - 1, 7) + 1 = WEEKDAY(da)) + (MOD(doi, 7) + 1 = WEEKDAY(da)) > 0, "X", IF(ISNUMBER(FIND(da & "", vdcomb)), "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, blnk), VSTACK(rone, rtwo, rthreeb, restop, blnk, t_r1, t_r2, t_r3, end, blnk)
    ),
    fintbl)

In practice, I'm envisioning that after some preliminary adjustments, the first five of the arguments will typically remain unchanged, and only "n" will change to display each month sequentially.

Then on each of the Shift worksheets, the following formula generates an array of month indexes, "midx". For example, when "cbeg" is 2/5/24 and "cend" is 7/15/24, then "midx" is {0;1;2;3;4;5}. Finally, this "midx" array is passed to the REDUCE LAMBDA helper function, which in turn calls the MonthCal function for each of the values in "midx" and vertically stacks the monthly outputs:

=LET(cbeg,Master!$J$2, cend,Master!$J$3, nmo,12*(YEAR(cend)-YEAR(cbeg))+MONTH(cend)-MONTH(cbeg)+1, midx,SEQUENCE(nmo,,0),
IFERROR(DROP(REDUCE("",midx,LAMBDA(a,b,VSTACK(a,MonthCal(cbeg, cend, Lists!$C$2#, Lists!$D$2#, $A$1, b)))),1,0),""))

The Lists worksheet:

MrExcel_FLdave12_20240624.xlsx
	A	B	C	D
1	Shifts for Non-Supervisors	Shifts for Supervisors	Position	Days Off
2	1st	1st	Shift Supervisor	SM
3	2nd	2nd	Team Lead	MT
4	3rd	3rd	Classification Specialist	TW
5		Floating	Job Assignment Specialist	WT
6			Reclassification Specialist	TF
7				FS
8				SS
Lists

Cell Formulas
Range	Formula
A2:A4	A2	=LET(a,SEQUENCE(3),a&CHOOSE(a,"st","nd","rd"))
B2:B5	B2	=VSTACK(A$2#,"Floating")
C2:C6	C2	={"Shift Supervisor";"Team Lead";"Classification Specialist";"Job Assignment Specialist";"Reclassification Specialist"}
D2:D8	D2	={"SM";"MT";"TW";"WT";"TF";"FS";"SS"}
Dynamic array formulas.

The Master worksheet:

MrExcel_FLdave12_20240624.xlsx
	A	B	C	D	E	F	G	H	I	J
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			Term Begins	2/5/2024
3	Shift Supervisor	delta	2nd	TF	3/2/2024, 3/11/2024, 4/3/24-4/6/24	6/26/24-7/7/24			Term Ends	7/15/2024
4	Shift Supervisor	lima	Floating	WT
5	Shift Supervisor	mike	3rd	WT	3/11/2024-3/16/2024	2/11/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
8	Team Lead
9	Job Assignment Specialist	alpha	2nd	SS	3/2/2024-3/11/2024
10	Job Assignment Specialist	kilo	1st	MT	4/7/24-4/11/24
11	Job Assignment Specialist	bravo	3rd	TW	4/15/2024-4/21/2024	5/14/2024-5/24/2024
12	Reclassification Specialist	india	2nd	TF
13	Reclassification Specialist	echo	1st	TF	3/3/2024-3/6/2024	3/27/2024-3/30/2024
14	Reclassification Specialist
15	Classification Specialist	golf	2nd	TW		2/18/2024
16	Classification Specialist	hotel	1st	FS
17	Classification Specialist	oscar	3rd	MT
18	Classification Specialist	november	2nd	WT		6/15/24-6/19/24
Master

Cells with Data Validation
Cell	Allow	Criteria
C2:C18	List	=ddShiftList
D2:D18	List	=Lists!$D$2#
A2:A18	List	=Lists!$C$2#

A portion of the 1st (shift) worksheet (other months spill further below, but not included due to space limitations). This same formula would be copied to the 2nd and 3rd worksheets to display monthly calendars for those shifts:

MrExcel_FLdave12_20240624.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
1	Monthly Staffing (Shift: 1st, Period: 2/5/24–7/15/24)
2
3	Feb-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
4	1st Shift		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	T
5	Supervisor/Team Lead/Specialist Positions
6	Shift Supervisor	foxtrot					X	X						X	X						X	X						X	X
7	Team Lead	juliet										X	X						X	X						X	X
8	Classification Specialist	hotel									X	X						X	X						X	X
9
10	Total Supervisors/Team Leads		2	2	2	2	1	1	2	2	2	1	1	1	1	2	2	2	1	1	1	1	2	2	2	1	1	1	1	2	2
11	Total Classification Specialists		1	1	1	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
12	Total on Shift		3	3	3	3	2	2	3	3	2	1	2	2	2	3	3	2	1	2	2	2	3	3	2	1	2	2	2	3	3
13
14	Classification Administrative Positions	Members
15	Job Assignment Specialist	kilo					X	X						X	X						X	X						X	X
16	Reclassification Specialist	echo								X	X						X	X						X	X						X
17
18	Total Job Assignment 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
19	Total Reclassification Specialists		1	1	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
20	Total on Shift		2	2	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	1
21	end
22
23	Mar-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
24	1st Shift		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	T	F	S	S
25	Supervisor/Team Lead/Specialist Positions
26	Shift Supervisor	foxtrot				X	X						X	X						X	X						X	X
27	Team Lead	juliet		X	X						X	X						X	X			V			X	X						X	X
28	Classification Specialist	hotel	X	X						X	X						X	X						X	X						X	X
29
30	Total Supervisors/Team Leads		2	1	1	1	1	2	2	2	1	1	1	1	2	2	2	1	1	1	1	1	2	2	1	1	1	1	2	2	2	1	1
31	Total Classification 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
32	Total on Shift		2	1	2	2	2	3	3	2	1	2	2	2	3	3	2	1	2	2	2	2	3	2	1	2	2	2	3	3	2	1	2
33
34	Classification Administrative Positions	Members
35	Job Assignment Specialist	kilo				X	X						X	X						X	X						X	X
36	Reclassification Specialist	echo	X		V	V	V	V	X	X						X	X						X	X					V	X	X	V
37
38	Total Job Assignment 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
39	Total Reclassification Specialists		0	1	0	0	0	0	0	0	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	0	0	0	0	1
40	Total on Shift		1	2	1	0	0	1	1	1	2	2	1	1	2	1	1	2	2	1	1	2	1	1	2	2	1	1	1	1	1	1	2
41	end
42
1st

Cell Formulas
Range	Formula
A1	A1	=CONCAT("Monthly Staffing (Shift: ",TEXTAFTER(CELL("filename",$A$1),"]"),", ", "Period: ",TEXT(Master!J2,"m/d/yy"),"–",TEXT(Master!J3,"m/d/yy"),")")
A3:AG122	A3	=LET(cbeg,Master!$J$2, cend,Master!$J$3, nmo,12*(YEAR(cend)-YEAR(cbeg))+MONTH(cend)-MONTH(cbeg)+1, midx,SEQUENCE(nmo,,0), IFERROR(DROP(REDUCE("",midx,LAMBDA(a,b,VSTACK(a,MonthCal(cbeg, cend, Lists!$C$2#, Lists!$D$2#, $A$1, b)))),1,0),""))
Dynamic array formulas.

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

A full working version is available at:

Dropbox

www.dropbox.com

 

[KRice]

In the previous post I mentioned how the MonthCal LAMBDA function could be set up using Excel's Advanced Formula Environment. Alternatively, it could be established using the more traditional Name Manager. These two approaches differ in what should be entered for the function. For long formulas, I tend to rely on the AFE rather than the Name Manager. For reasons that I do not fully understand, the formula entered in the AFE is not quite the same as that entered into the Name Manager. The AFE version requires the LET formula, but AFE "understands" that the formula is a LAMBDA function, so the function "LAMBDA" (the word itself) and its argument list are not entered in the Function Definition field...only the main LET part of the formula is entered in the Function Definition, and the argument list is entered separately into the Arguments field, separated by commas. The Name Manager, on the other hand, requires that the entire function (=LAMBDA(argument list, LET...) must be entered into the Refers to: field. For some reason, the Name Manager does not appear to display the formula correctly in the editable Refers to:, while the summary display (not editable) of named variables does appear to display the formula correctly in its "Refers to" column.

Here is an example of the MonthCal function being used as a stand-alone LAMBDA to return the results for the third month in the bid period specified (note the "2" in the argument list passed to the function in the parentheses following the function, and recall that the month index is a 0-basis index). In this example, a date in February 2024 was the beginning of the bid period, so n=2 means April 2024. I've replaced the posted working file with an updated version that includes this stand-alone LAMBDA on the 3rd shift tab and edited some 3rd shift entries on the Master sheet to illustrate functionality.

MrExcel_FLdave12_20240624.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
81	MonthCal LAMBDA function
82	monthly schedule of vacation and days off
83	arguments: cbeg,cend,pcat,dol,currshtcell,n
84
85	Apr-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
86	3rd 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
87	All Positions
88	Shift Supervisor	mike			X	X						X	X						X	X						X	X
89	Classification Specialist	oscar	X	X	V	V	V	V	V	X	X				V		X	X	V	V				X	X						X	X
90	Job Assignment Specialist	bravo		X	X						X	X					V	X	X	V	V	V	V		X	X						X
91
92	Total Supervisors/Team Leads		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
93	Total Classification Specialists		0	0	0	0	0	0	0	0	0	1	1	1	0	1	0	0	0	0	1	1	1	0	0	1	1	1	1	1	0	0
94	Total on Shift		2	1	0	1	2	2	2	2	1	1	2	3	2	3	1	1	0	0	2	2	2	2	1	1	2	3	3	3	2	1
95	end
96
3rd

Cell Formulas
Range	Formula
A85:AF96	A85	=LAMBDA(cbeg,cend,pcat,dol,currshtcell,n,LET( mobase, EOMONTH(cbeg, -1) + 1, mo, EDATE(mobase, n), da, SEQUENCE(1, DAY(EOMONTH(mo, 0)), mo), dow, CHOOSE(WEEKDAY(da), "S", "M", "T", "W", "T", "F", "S"), sht, TEXTAFTER(CELL("filename", currshtcell), "]"), 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), doi, MATCH(CHOOSECOLS(ary, 4), dol, 0), vall, CHOOSECOLS(ary, 5) & "," & CHOOSECOLS(ary, 6), vallsplit, IFERROR( TRIM(DROP(REDUCE(0, vall, LAMBDA(a,b, VSTACK(a, TEXTSPLIT(b, ",")))), 1, 0)), "" ), vd, MAP( vallsplit, LAMBDA(d, LET( begin, TEXTBEFORE(d, "-", , , , d) + 0, end, TEXTAFTER(d, "-", , , , d) + 0, reqd, IFERROR(SEQUENCE(end - begin + 1, , begin), ""), TEXTJOIN(",", 1, reqd) ) ) ), vdcomb, BYROW(vd, LAMBDA(r, TEXTJOIN(",", 1, r))), vdo, IF( (da < cbeg) + (da > cend) > 0, "", IF( (MOD(doi - 1, 7) + 1 = WEEKDAY(da)) + (MOD(doi, 7) + 1 = WEEKDAY(da)) > 0, "X", IF(ISNUMBER(FIND(da & "", vdcomb)), "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, blnk), VSTACK(rone, rtwo, rthreeb, restop, blnk, t_r1, t_r2, t_r3, end, blnk) ), fintbl ))(Master!$J$2,Master!$J$3,Lists!$C$2#,Lists!$D$2#,$A$1,2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3,A4:AG80,A84:AG200	Expression	=$B3="Members"	text	NO
A3,A4:AG80,A84:AG200	Expression	=OR($A3="1st Shift",$A3="2nd Shift",$A3="3rd Shift",$A3="Supervisor/Team Lead/Specialist Positions")	text	NO
A3,A4:AG80,A84:AG200	Expression	=OR($A3="Total Supervisors/Team Leads",$A3="Total Classification Specialists",$A3="Total on Shift",$A3="Total Job Assignment Specialists",$A3="Total Reclassification Specialists")	text	NO
A3,A4:AG80,A84:AG200	Expression	=$A3="end"	text	NO
A83	Expression	=$B82="Members"	text	NO
A83	Expression	=OR($A83="1st Shift",$A83="2nd Shift",$A83="3rd Shift",$A83="Supervisor/Team Lead/Specialist Positions")	text	NO
A83	Expression	=OR($A83="Total Supervisors/Team Leads",$A83="Total Classification Specialists",$A83="Total on Shift",$A83="Total Job Assignment Specialists",$A83="Total Reclassification Specialists")	text	NO
A83	Expression	=$A83="end"	text	NO
B81:AG83	Expression	=$B81="Members"	text	NO
B82:AG82	Expression	=OR($A83="1st Shift",$A83="2nd Shift",$A83="3rd Shift",$A83="Supervisor/Team Lead/Specialist Positions")	text	NO
B82:AG82	Expression	=OR($A83="Total Supervisors/Team Leads",$A83="Total Classification Specialists",$A83="Total on Shift",$A83="Total Job Assignment Specialists",$A83="Total Reclassification Specialists")	text	NO
B82:AG82	Expression	=$A83="end"	text	NO
B81:AG81,B83:AG83	Expression	=OR(#REF!="1st Shift",#REF!="2nd Shift",#REF!="3rd Shift",#REF!="Supervisor/Team Lead/Specialist Positions")	text	NO
B81:AG81,B83:AG83	Expression	=OR(#REF!="Total Supervisors/Team Leads",#REF!="Total Classification Specialists",#REF!="Total on Shift",#REF!="Total Job Assignment Specialists",#REF!="Total Reclassification Specialists")	text	NO
B81:AG81,B83:AG83	Expression	=#REF!="end"	text	NO

Dropbox

www.dropbox.com

 

[FLdave12]

Kirk,

Thank you very much for your help, knowledge and expertise. Everything seems to be working really well.

 

[KRice]

You’re welcome…glad to hear it’s working!