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]

Yes 13 rows for position/members/results will be sufficient. Will the totals area for 1st shift separating as show be possible?

 

[KRice]

Will the totals area for 1st shift separating as show be possible?

What does this mean? For each month, regardless of the shift, there is a 3-row totals area, correct? But I don't understand what you mean by "separating"...am I missing something?

Edit: I must be missing something. The 1st shift monthly blocks appear to have multiple sub-blocks within them. A 13-row block followed by a yellow row followed by a 3-row totals sub-block (this much I expected)...but then there is a blank row followed by another members list. I don't understand the purpose for these sub-blocks.

Master Bid idea 11--28-23.xlsx
	A	B	C	D
64	Mar-24		1	2
65	1st Shift	Members	F	S
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80	Total Supervisor/Team Leads
81	Total Classification Specialists
82	Total on Shift
83
84	Classification Administrative Positions	Members
85	Job Assignment Specialist
86	Job Assignment Specialist
87	Reclassification Specialist
88
89	Total Job Assignment Specialists
90	Total Reclassification Specialists
91	Total on Shift
1st

 

[FLdave12]

1st Shift tab totals for Shift Supervisor/Specialist and then for Job Assignment and Reclass see attached

 

[FLdave12]

 

[FLdave12]

The Job Assignment and Reclass positions are only on 1st shift and are specialized positions. They are counted separately from the Supervisor/Team Lead & Classification Specialists.

 

[KRice]

Okay, I think I understand. So for the 1st shift, you'd like to see these four sub-blocks for each month:

1. Member list for Supervisors and Team Leads, as well as Classification Specialists (all together in the upper sub-block, perhaps with Supervisors/Team Leads appearing first?)
2. A 3-row total of the #1 sub-block, broken down to show Supervisors/Team Lead totals, Classification Specialists totals, and the sum of these position categories
3. Member list for "Classification Administrative Positions" to include only Job Assignment Specialists and Reclassification Specialists
4. A 3-row total of the #3 sub-block, broken done to show Job Assignment Specialists totals, Reclassification Specialists totals, and the sum of these position categories

Is this correct?

This complicates matters...I'll have a look.

 

[FLdave12]

That is correct but if too complicated would it be easier to exclude Job Assignment and Reclassification in the Totals area. Meaning just count Supervisor,Team Lead, Classification specialists and then total of both

 

[FLdave12]

Don't know if I mentioned that when I put vacation time on tblmaster sheet it filled in on January month even though vacation time was for month of March

 

[KRice]

...when I put vacation time on tblmaster sheet it filled in on January month even though vacation time was for month of March

What did you enter for vacation time?...specifically interested in the date format and how you plan to show a date range.

 

[FLdave12]

3-4-23 to 3-10-23