Excel formula needed

F

FLdave12

Board Regular
Joined
Feb 4, 2022
Messages
73
Platform
  1. Windows
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.
 
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
KRice said:
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?
Click to expand...
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))
 
Upvote 0
FLdave12 said:
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))
Click to expand...
The XL2BB add-in install does not work
 
Upvote 0
1701099428767.png


Highlighted areas seem to be where problem in formula
 
Upvote 0
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?
 
Upvote 0
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.
Excel Formula: 
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)
 
Upvote 0
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
21st
33rd
42nd
51st
62nd
71st
80
92nd
102nd
111st
122nd
131st
140
153rd
161st
173rd
182nd
Master
Cell Formulas
RangeFormula
I2:I18I2=tblMaster[Shift]
Dynamic array formulas.
 
Last edited:
Upvote 0
KRice said:
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
21st
33rd
42nd
51st
62nd
71st
80
92nd
102nd
111st
122nd
131st
140
153rd
161st
173rd
182nd
Master
Cell Formulas
RangeFormula
I2:I18I2=tblMaster[Shift]
Dynamic array formulas.
Click to expand...
KRice said:
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
21st
33rd
42nd
51st
62nd
71st
80
92nd
102nd
111st
122nd
131st
140
153rd
161st
173rd
182nd
Master
Cell Formulas
RangeFormula
I2:I18I2=tblMaster[Shift]
Dynamic array formulas.
Click to expand...
I changed the name to tblMaster. How do I change the row labels? When I tried to Insert Table the icon is grey out.
 
Upvote 0
The rows do not have labels...the columns do. You change the column labels by selecting the cell and retyping with the desired name.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
195
Peter_SSs
Peter_SSs
M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
219
MrsGrayMarlin
M
F
  • Question Question
Pivot Table
Replies
2
Views
411
FLdave12
F
J
  • Solved
Assistance with Index Match formula
Replies
2
Views
202
Jay88
J
F
  • Question Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
439
FajnaAli
F

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top