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]

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

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

See snippet---highlighted areas---Table Name is now tblMaster. I ran =tblMaster[Shift] in cell G and results are 0

 

[FLdave12]

See snippet---highlighted areas---Table Name is now tblMaster. I ran =tblMaster[Shift] in cell G and results are 0

 

[FLdave12]

Since this is not working XL2BB would it be possible to email the workbook?

 

[KRice]

That looks okay now. I see an empty array is being returned. That's expected because no shifts have been designated for the members in the table. Edit the table to shows some members assigned to 1st, 2nd, and 3rd shifts and see if that single column array off to the right updates.

About emailing...no, I believe the preferred approach is to post via XL2BB, and if that becomes problematic, then the file can be uploaded to any of several file-sharing locations (Box, Dropbox, Google Drive, etc.), and the link to the file can then be posted.

 

[FLdave12]

So that is working, however the A7 formula still does not. I haven't tried the totals area formula yet

 

[KRice]

You need to provide data so that the formula has something to process. For the 3 people added to the table, specify their days off and enter vacation blocks for some of them. Or copy/paste my table from the earlier post.

 

[FLdave12]

So when I entered name, shift, and days off on the tblmaster sheet the information fill in on the appropriate shift sheets for the month of January. The rest of the months did not
fill in. Also, I put in vacation time for the month of February but it filled in on January month. So still need help there.

Is it possible to have the year and month dates and days autofill on the other months (IE) February, March, April, May, June and July) as it does for January?

Also, if I get the most recent version of Excel that is not 365 will these formulas work?

Thank you again for all your help. It is much appreciated.

 

[FLdave12]

I will post via Google Drive tomorrow

 

[FLdave12]

Loading Google Sheets

Please advise if document is there and can be edited.

Thanks

 

[KRice]

It is there, downloadable, and editable.

What can you say about the multi-month blocks? Right now, the formula returns results for the month-year specified in B1:B2. For any given month, I see that you've reserved 13 rows for position/members/results to spill. Then a blank yellow row appears followed by the orange 3-row totals. Will 13 rows always be sufficient to capture the results? I ask because there are different approaches that could be taken to populate the additional months. One is to simply insert a spilling formula in each month block. Other approaches vary in complexity but could adapt to some degree to changes in the lengths of the output blocks.