Forum Discussion
Returning specific cell contents across a range
I work in a school and have a master timetable in excel. What I want to do, is to create individual class timetables based on the master timetable. My problem is that individual timetables are one cell per period, but the master is 9 cells per period on another sheet to keep specialist teachers separate. Each class has a name at the start of the lesson, ie 1EX then the subject name, so I would like each cell in the individual timetable to look through the 9 cells that make up that class period in the master then return the contents of that cell if it is found, leaving it blank if not found.
In my example, I want the individual timetable to search B2:B10 for anything containing 1EX and return only that particular cell's contents in F2 and so on.
Thanks in advance for any help!
ETA: Better description and file example in comment below.
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.
6 Replies
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.
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
* Knowing the Excel version and operating system would also be an advantage.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Thanks for the suggestion to attach a file instead of screenshots. I have done that now. I'm on a Mac running Big Sur 11.01 and Excel for Mac version 16.41.
My process (at the moment - I am very open to suggestions about how to do this more efficiently) is to enter individual specialist teacher timetables onto Specialist Teachers (1). This also puts them into a basic table XX Just Lessons (1.5) XX and XX Combined Timetable (2) XX.
What I want to happen next is have a series of sheets based on XX Class Teacher Master (3) XX where I can show just the specialist subjects for particular classes - ie all 2BC classes on the one small timetable. (It doesn't matter to me whether these 15 small class timetables are all on the one sheet or each has their own separate sheet.)
My aim is to be able to move and swap classes on the Specialist Teachers (1) sheet and see their impact on individual class timetables.
I suspect there is a much more elegant way of making this work, but it's beyond my knowledge at the moment.
