Pulling data from one tab to another- with an editable parameter

[TMcIlroy]

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!

[#2]

For better understanding,,, please share some sample data/Workbook along with expected output then I'll show you the method to achieve the goal!

[#3]

@Rajesh_Sinha   Ok- Ive added some images of current and goal.  Hope that helps! 

[#4]

@TMcIlroy ,,

This solves the issue:

Results:

How it works:

• In Master sheet from B5 to next the Column Name must similar to Sheet Name like Monday, Tuesday.
• Array (CSE) formula in Master Sheet's cell B6:

{=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)),"")}

• Finish formula with Ctrl+Shift+Enter, and fill across.
• Next you need to adjust cell references in this part, before you change the criteria in cell C1.

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.

[#5]

@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.