Oct 07 2020 12:03 AM - edited Oct 07 2020 12:26 AM
Hello,
I am organising a school timetable on excel. Days of the week are on tabs. With classes on columns. Lessons in rows. Each cell has data entered like this 1A/Eng/CMC (class code/Subject/Teacher code)
I want to create an additional tab where I can generate a full week timetable for a teacher or a class but pulling the data from the 5 daily tabs (Im aware it will be slightly different lookup formula for each tab) but is there a way I can make find all the times CMC shows up for example? Or adjust it so it finds all of the 1A sessions?
Can someone help with the formula for the pulling data into the weekly tab?
Is there a way I can build in a 'search' box into the weekly timetable generator tab- so someone types a class code, or a teacher code the full weekly timetable for that class or that teacher shows up? Or they can type their teacher code like CMC into the box and the teacher gets her timetable? I have seen this type of thing on other sheets (not for this purpose but I have seen it) and I really need teachers to be able to see their own timetables in a week view and to not have to make 63 of them manually!
Thank you!
Oct 07 2020 12:08 AM
Oct 07 2020 12:27 AM
@Rajesh_Sinha Ok- Ive added some images of current and goal. Hope that helps!
Oct 07 2020 03:33 AM
@TMcIlroy ,,
This solves the issue:
Results:
How it works:
{=IFERROR(INDEX(INDIRECT(B$5&"!$B$2:$B$8"),SMALL(IF(COUNTIF(INDIRECT(B$5&"!$B$2:$B$8"),"*"&$C$1&"*"),ROW(INDIRECT(B$5&"!$B$2:$C$8"))-MIN(ROW(INDIRECT(B$5&"!$B$2:$C$8")))+1),ROW($A1)),COLUMN($A1)),"")}
For GDA change this :
INDEX(INDIRECT(B$5&"!$C$2:$C$8"),SMALL(IF(COUNTIF(INDIRECT(B$5&"!$C$2:$C$8"),"*"&$C$1&"*")
Since GDA records are in Column C.
You may use IF to check criteria in cell C1, and accordingly for TRUE and FALSE use required data range but it will be complicated, so better adjust cell references as I've suggested above , every time you change criteria.
Oct 07 2020 05:31 AM
@TMcIlroy Merely to demonstrate what's possible with Power Query (PQ), even though I don't consider myself an expert in that area yet. Have a look at the attached workbook. I mocked-up a schedule for one week and introduced some extra classes. By combining structured tables and PQ, this becomes totally flexible and dynamic, without the need for complicated formulae. Expand the daily tables downwards or towards the right and all should update automatically.
Once you understand the mechanics, it's quite easy to maintain. Though, you would have to invest some time in learning PQ.
In the attached example, go to the "TeacherSchedule" sheet, select a teacher code in B2. Then, on the Data ribbon press "Refresh All" (can also be automated) and the table gets updated almost instantly.
Note: The sheet "DayPlan and Teachers" contains a list of time slots (from 7:30 - 16:00) and a list of teacher codes. Adjust these to suit your actual situation.