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]

Let's ignore the "other months" issue until after you get the A7 formula working. I mentioned that the solution provided does only a single month. Is it your expectation that cell A34 would begin a new output table for March 2024. This is what I asked about in post #17...I'm not clear on what output is desired.

Are you able to install the XL2BB add-in? Using it greatly facilitates assistance because you would be able to post a small section of your workbook with formulas included. I need to be able to see the formula in your cell A7 and any cells that are referenced by it to help diagnose the problem. Did you copy the formulas to your clipboard and paste them directly into your spreadsheet, or did you retype the formulas? The XL2BB add-in has a feature that allows you to click on the icon in the upper left of the posted worksheet snippet...that copies everything in the snippet to your clipboard. And then you can perform a single paste operation into the appropriate cell of your workbook, which greatly speeds up the process of getting the worksheet working on your end.

For now, could you post the A7 formula taken directly from your worksheet?

 

[FLdave12]

Let's ignore the "other months" issue until after you get the A7 formula working. I mentioned that the solution provided does only a single month. Is it your expectation that cell A34 would begin a new output table for March 2024. This is what I asked about in post #17...I'm not clear on what output is desired.

Are you able to install the XL2BB add-in? Using it greatly facilitates assistance because you would be able to post a small section of your workbook with formulas included. I need to be able to see the formula in your cell A7 and any cells that are referenced by it to help diagnose the problem. Did you copy the formulas to your clipboard and paste them directly into your spreadsheet, or did you retype the formulas? The XL2BB add-in has a feature that allows you to click on the icon in the upper left of the posted worksheet snippet...that copies everything in the snippet to your clipboard. And then you can perform a single paste operation into the appropriate cell of your workbook, which greatly speeds up the process of getting the worksheet working on your end.

For now, could you post the A7 formula taken directly from your worksheet?

I copied and pasted the formula: Here is the snippet:

=LET(sht,TEXTAFTER(CELL("filename",A1),"]"),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),{"SM","MT","TW","WT","TF","FS","SS"},0),
vdo,IF(((C5#>=v1b)*(C5#<=v1e)+(C5#>=v2b)*(C5#<=v2e)>0),"V",
IF(((MOD(doi-1,7)+1=WEEKDAY(C5#))+(MOD(doi,7)+1=WEEKDAY(C5#))>0),"X","")),
HSTACK(pm,vdo))

 

[FLdave12]

I copied and pasted the formula: Here is the snippet:

=LET(sht,TEXTAFTER(CELL("filename",A1),"]"),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),{"SM","MT","TW","WT","TF","FS","SS"},0),
vdo,IF(((C5#>=v1b)*(C5#<=v1e)+(C5#>=v2b)*(C5#<=v2e)>0),"V",
IF(((MOD(doi-1,7)+1=WEEKDAY(C5#))+(MOD(doi,7)+1=WEEKDAY(C5#))>0),"X","")),
HSTACK(pm,vdo))

The XL2BB add-in install does not work

 

[FLdave12]

Highlighted areas seem to be where problem in formula

 

[KRice]

Could you post a screenshot showing your master table. I'm curious...has that table been converted into an official Excel table, and what format is being used to denote the vacation dates?

 

[FLdave12]

 

[KRice]

Here is a slightly modified version of the formula. Paste this below any content on your worksheet so that there is plenty of room for results to spill down and to the right. Then change the last part of the formula where we tell the formula which variable to output so that you can check where the problem lies. So as a first step, the words "type output variable name here" would be replaced with sht (the first variable in the formula)...and you should see the formula return the name of your worksheet (which should be either 1st, 2nd, or 3rd). Repeat for any variable that needs to be investigated. For example, ary as the last term in the formula should output a filtered list from your Master table, showing only those rows where members' shifts match the sheet name. I suspect the problem lies with your Master table. What you've just posted does not show a column named Shift. What is your Master table named? The formula assumes you've named it tblMaster and that it is structured like the one shown in my Post #10.

LET(sht,TEXTAFTER(CELL("filename",A1),"]"),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),{"SM","MT","TW","WT","TF","FS","SS"},0),
vdo,IF(((C5#>=v1b)*(C5#<=v1e)+(C5#>=v2b)*(C5#<=v2e)>0),"V",
IF(((MOD(doi-1,7)+1=WEEKDAY(C5#))+(MOD(doi,7)+1=WEEKDAY(C5#))>0),"X","")),
res,HSTACK(pm,vdo),
type output variable name here)

 

[KRice]

To confirm the name of the Master table, click anywhere in the table and you should see a Table Design menu item appear at the top of your screen. Click Table Design and you should see a submenu appear for Table Properties...the Table Name will appear there...and can be changed there.

To clarify my comment about your Master table, I believe the 1st row labels are not part of your table. Your table column headings appear to be Shift Supervisor, Column1, Column2,... rather than Position, Member, Shift, Days Off, 1st Approved Vacation, and 2nd Approved Vacation. To double check this, go to an empty cell and enter =tblMaster[Shift]
Here is what I get:

MrExcel_20231122.xlsx
	I
2	1st
3	3rd
4	2nd
5	1st
6	2nd
7	1st
8	0
9	2nd
10	2nd
11	1st
12	2nd
13	1st
14	0
15	3rd
16	1st
17	3rd
18	2nd
Master

Cell Formulas
Range	Formula
I2:I18	I2	=tblMaster[Shift]
Dynamic array formulas.

 

[FLdave12]

To confirm the name of the Master table, click anywhere in the table and you should see a Table Design menu item appear at the top of your screen. Click Table Design and you should see a submenu appear for Table Properties...the Table Name will appear there...and can be changed there.

To clarify my comment about your Master table, I believe the 1st row labels are not part of your table. Your table column headings appear to be Shift Supervisor, Column1, Column2,... rather than Position, Member, Shift, Days Off, 1st Approved Vacation, and 2nd Approved Vacation. To double check this, go to an empty cell and enter =tblMaster[Shift]
Here is what I get:

MrExcel_20231122.xlsx
	I
2	1st
3	3rd
4	2nd
5	1st
6	2nd
7	1st
8	0
9	2nd
10	2nd
11	1st
12	2nd
13	1st
14	0
15	3rd
16	1st
17	3rd
18	2nd
Master

Cell Formulas
Range	Formula
I2:I18	I2	=tblMaster[Shift]
Dynamic array formulas.

To confirm the name of the Master table, click anywhere in the table and you should see a Table Design menu item appear at the top of your screen. Click Table Design and you should see a submenu appear for Table Properties...the Table Name will appear there...and can be changed there.

To clarify my comment about your Master table, I believe the 1st row labels are not part of your table. Your table column headings appear to be Shift Supervisor, Column1, Column2,... rather than Position, Member, Shift, Days Off, 1st Approved Vacation, and 2nd Approved Vacation. To double check this, go to an empty cell and enter =tblMaster[Shift]
Here is what I get:

MrExcel_20231122.xlsx
	I
2	1st
3	3rd
4	2nd
5	1st
6	2nd
7	1st
8	0
9	2nd
10	2nd
11	1st
12	2nd
13	1st
14	0
15	3rd
16	1st
17	3rd
18	2nd
Master

Cell Formulas
Range	Formula
I2:I18	I2	=tblMaster[Shift]
Dynamic array formulas.

I changed the name to tblMaster. How do I change the row labels? When I tried to Insert Table the icon is grey out.

 

[KRice]

The rows do not have labels...the columns do. You change the column labels by selecting the cell and retyping with the desired name.