Automating Selections from Sequential Lists

[iancobb]

Good day,

I am trying to reduce the number of adjustments I have to make on various daily assignment and scheduling sheets for my place of work. A few months ago, I brought my problem to the Excel community as that is the program provided by my employer; a couple of fellows tried to help, but their solutions either did not work or did not meet my need. (Link to page below!).
https://answers.microsoft.com/en-us/msoffice/forum/all/require-assistance-with-excel-formulae/99d606f0-1b29-44c9-a76f-f7378318fa34

Since I use Linux/LibreOffice at home–Microsoft and Apple products/applications are generally outlawed in my household–I thought I might inquire here. I reckon that, if someone has a solution for me in LibreOffice Calc, I can probably work out a similar solution for Excel. (Or, if nothing else, I can at least say I tried and maybe learn something along the way.)

My workflow is as follows: I have a Period Schedule, which lists all of the department employees and their scheduled days, and a daily assignment sheet, which is–as it sounds–a breakdown of the role of each employee for that shift (i.e. on which wing and on which floor an individual will work).

The Period Schedule has a vertical column with each employee’s name on a separate cell and horizontal column with the days of the week; the cross-sections include an “X” for each day/shift an employee works. The daily assignment sheets list the employee names next to their designated wings. Refer to the [crude] example below:

Period Schedule
Monday Tuesday Wednesday

George X X

Teddy X X

Abraham X X

Monday Daily Assignments

North Wing: George
South Wing: Abraham

Tuesday Daily Assignments

North Wing: Teddy
South Wing: Abraham

Currently, I have to fill out the daily assignment sheets manually; my goal is to utilize an equation that results in the name of each employee scheduled on a given day populating on the appropriate daily assignment sheet. (Ideally, the names would be selectable from a dropdown menu; however, if they populated as a secondary list from which I would have to pull manually, that would still save me time and reduce mistakes.)

If someone has an answer to this problem, I’d be much obliged.

Thank you very much!

[Lupp]

What did I miss?
I did not understand how the"daily assignment sheet" distinguishing the “wings” might be created possibly if the primary information doesn’t contain anything about the"wing". No routine should be allowed to “invent” the assignment to a wing, should it?
I also didn’t clearly understand the “…from sequential lists”.

[iancobb]

If I can explain it simply… I have a list of employee names in Column A. In the top row, I have a series of days–twice Sunday through Saturday. An “X” is placed at the cross-sections whenever an employee is scheduled to work on a given day.

My goal is to have a series of secondary lists–one for each day of each week–that is comprised of only the names of the employees scheduled to work on that day. I would then take from that list to assign the daily assignments–the wings, as you stated. That would be done manually and would not be represented within the equation. I hope that resolves any confusion.

[JohnSUN]

In other words, do you want to get from the rectangular matrix a specific value for two variables - the name of the employee and the day of the week? Please look at this formula. It seems to me that the solution to your problem was described five years ago.

Or maybe this solution will satisfy you - Automating Selections from Sequential Lists.ods

[iancobb]

I did not understand the first potential solution you provided to me. The thread contained little in the way of an explanation, and I generally do not know what I am doing, so…

The second link, however, seems to satisfy my need completely. It was more or less a plug-and-play solution, so I give you my sincerest “Thank you!”. Unfortunately, the version of Excel used at my office does not support the “textjoin” function, so I might be back to the drawing board with this. But, I always appreciate the chance to learn something, so–again–thank you to everyone for your assistance.

[Lupp]

Despite my probably insufficient understanding I tried a solution where the matrix doesn’t just contain "X"es, but info about the location (wing).
See this attachment.