1

I am trying to create a rotating schedule that would allow for the drop down on "daily schedule" sheet to populate the A, B, C, and D schedules listed on sheet "ABCD" which were populated using vlookup from "input classes" sheet.

I've tried vlookup, index/match, and indirect but can't seem to figure out how to have the drop down populate the specific schedule listed for that day. Any advice would help!

Emma
  • 13
  • 3

2 Answers2

0

Try this: https://docs.google.com/spreadsheets/d/1D_TBOUHVfTvCdvVi6oM43ZhZqFcKyn52mzuFjAeNBZs/edit?usp=sharing

The key is that the drop down values don't match the values in the schedule. If you instead just take the first character from the drop down cell (ie. for "A DAY SCHEDULE", just take "A"), and then search for that in the schedule sheet, then it's very straightforward

Mark
  • 7,785
  • 2
  • 14
  • 34
0

In cell B4 under Period 1 column in the Daily Schedule sheet, use this function:

=INDEX(ABCD!$C$4:$K$7, 
 MATCH(left($B$1,1), 
 ABCD!$B$4:$B$7,0), 
 int(right(ABCD!C3,1)))

Then, copy the formula over the rest of the cells and it should work.

Explanation: I've used INDEX and MATCH functions to match the first letter of your drop-down cell (using the LEFT function) with the DAY column in the ABCD sheet. In order to specify the column number to get the desired cell, I've used the last character of each one of your PERIOD columns, which conveniently are ordered numbers, and converted them to integers using INT and RIGHT functions [Example: INT(RIGHT('Period 1', 1)) --> 1].

AxelOruse
  • 46
  • 3