Forum Discussion
Returning specific cell contents across a range
Like Riny_van_Eekelen I am not absolutely sure of the significance of elements of master table such as the 1EX. I have taken it as a teacher ID but it might be open to other interpretation. Since the workbook functioned at all, I assume you have an up to date version of Microsoft 365 on your Mac.
The attached file has been changed to output the normalised data form I used within Power Query. I also modified the formulas to use lesson numbers which are now created by formula rather than being text labels.
The defined Names used on each worksheet are local names, specific to the sheet. That means that copying the sheet simply creates a new set of names and the formulas should work correctly.
I completed a workbook earlier in the day but it relies upon Microsoft 365 for its functionality.
Otherwise, I might go for Power Query but you may have problems either way with a Mac.
That works so well - thank you! My only question now is how to add the other classes (not in the example timetable) can I just copy the sheets you've created and change the class name?
Like Riny_van_Eekelen I am not absolutely sure of the significance of elements of master table such as the 1EX. I have taken it as a teacher ID but it might be open to other interpretation. Since the workbook functioned at all, I assume you have an up to date version of Microsoft 365 on your Mac.
The attached file has been changed to output the normalised data form I used within Power Query. I also modified the formulas to use lesson numbers which are now created by formula rather than being text labels.
The defined Names used on each worksheet are local names, specific to the sheet. That means that copying the sheet simply creates a new set of names and the formulas should work correctly.
lajabu As PeterBartholomew1 suggested Power Query could also perform the task. However, you will not be able to set it up on a Mac. But once created on a PC, you can refresh the queries on a Mac. The attached file contains a connection to named range "teachers" in the first tab and several queries that ultimately create the table in the "ClassSchedule" tab. Change something within the "teachers" range, go to the "ClassSchedule" and press "Refresh all". This will instantly update the schedule. Filter out the class you want to look at and you have your Class / Teacher schedule for the week. Now, all of this can be refined further, but I haven't gone so far. And perhaps, this is not an option for you att all as you will not be able to change the queries without access to Excel for Windows.
By the way, PowerQuery "spotted" potential errors in your example data and didn't like it. On Friday, classes 1AA and 1AB have two lessons simultaneously by different teachers. I marked them red in the teachers tab and deleted on of each to make it work. But perhaps I've misunderstood the set-up altogether.
