Pull data from long-form schedule to single day sheet

[D.C.Ballard]

I am desperate for help and am far out into the deep end without my water-wings.

I have a schedule that is in a long form and need to generate a daily post-able schedule from that, with a pre-designated layout, which I cannot change. I cannot restructure either sheet, as doing so is well above my pay-grade. I have been given a 'Make it work" assignment, and am drowning.

Sheet1 has the names in one column, and the full schedule for the month laid out to the right, each column being a day of the month, and the cell values being the work area assigned that day. The values-0p90 entered into Sheet 1 daily columns are values 1-6, L (Lead), and D_1-6/1-6 (For double shifts assigned, the _ being replaced with an E, N, or A, depending on the normal shift worked by the person and the second shift they will be working.)

I need to pull the names from Sheet1 (Entire Month) into cells in sheet2 (Daily printed schedule), based on the values in the day assignment columns to the right of the names on Sheet1.

On Sheet2 I have a column that lists the designated work areas. I need to pull the name for the person scheduled for that area into Sheet2, based on what they are assigned on Sheet1 for that day, based on which column to look at as selected via a Cell on Sheet2, such as Day3 or just 3, which corresponds to the day# of the column on Sheet1 that needs to be looked at to pull the names from the static name column on Sheet1.

I was able to determine the correct column number, using a MATCH(), but don’t know to do the rest of the manipulation/lookups needed to pull what I need. ALL suggestions are welcomed and appreciated

Note: Due to security setups, I cannot use Macro’s for this, it has to be done with some kind of formula, even if that is pulled into another cell set and then repopulated into where I need. I can have hidden sheets and cells. Hope that helps.Schedule_V1_scrubbed.ods

[Hrbrgr]

Can you upload your file anonymously here?
To upload edit your initial question.
Please do not write an answer that is not an answer to your input request.

[D.C.Ballard]

Done. I hope that helps.

[Lupp]

Sorry. Your sheets are mainly coloured paper in software representation.

Start data-centered! This includes,e.g. not to split an information like the date into three cells in a complicated way, and many things more, of course. Data themselves should never be interrupted by subtotals, or something alike.
Anyway: If you think to need a self-colouring table like the one in your first sheet for the creative part of your work, reorganise the data once filled in in a second sheet by formulae in a way better usable for further evaluation and for creating prettyprint oriented sheets. Also create special tables for your primary data like names and check your entries for correctness (by CF e.g. again). …

If a shift needs the assignment of more than one person due to different tasks (“Sections”?), create rows for the tasks (1 to 6, M, L and so on) and enter the ID of the person assigned into your 2D-schedule.

I would even suggest to use a row per day, and a column per Section. This would allow to continue with adding days for a long time and to select years, months, weeks, whatever from that collection for evaluations while the number of columns is limited to 1024 and 1000 days are not a long time.

It may then be easier to base the next step on that representation.

===Edit 2019-08-05 00:00 UTC===
Well, despite my valid arguments against the design I made a rough attempt to get what you were ordered.
Study this reworked spreadsheet document and complete the solution for the other shifts.
Or find a better solution …
Anyway your boss should call me.

[D.C.Ballard]

I appreciate the response, but you clearly missed this part: I cannot restructure either sheet, as doing so is well above my pay-grade. I have been given a 'Make it work" assignment, and am drowning.

I inherited this, and have to “Make it work” with what I am given. It is a schedule, as noted. I don’t have option but to make it work. Your comment is therefore very far from even remotely helpful.

[Lupp]

Sorry.
I feel to be rather experienced with spreadsheets and even did roughly similar things based on them when I was still in professional work. Nonetheless I wouldn’t know a way to advise you how to get a solution beyound some hints like “Make a helper sheet.”, “Use INDEX() or OFFSET()”, and a few more which most likely won’t suffice. Thus I would need to actually solve the task for you to be helpful.
Should I do so? Hmmm…
A boss should know what he reasonably can order to “Make it work”, and that should be worth the effort.

The given MainSheet doesn’t even make clear if the month given by its name (a silly idea in itself) belongs to the first day in the sequence or to the majority of them or to whatever…
In fact the sheet isn’t made fort a month but for five weeks…