looking up across sheets?

[Mosomiv]

Hi there,

I am a teacher trying to work on next year's timetable, and I am allocating staff to classes.

On Sheet 1 I have the classes listed with the number of lessons a fortnight:

In column C I will type the teachers 3-letter code so I know who is teaching that class:

So far so good.

Now in Sheet 2 I want the teachers to be allocated their classes, so I can see how many lessons that teacher has (what their load is):

I want column B and C to be filled from Sheet 1.

I enter the teachers codes in Sheet 1 against the class, and Sheet 2 is automatically populated with the class code and the number of lessons against the appropriate teacher.

To help with doing the sums, I wouldn't mind if Sheet 2 looked like this:

but that's not the end of the world if it doesn't.

Any thoughts on how to do this? Any help would be much appreciated.

Thanks

[#2]

@Mosomiv 

To further complicate matters, i need this to work for all the classes, so there will be multiple columns

eg

So the Maths staffing may end up looking like this (for Years 7 & 8 for example):

Again, any help or suggestions would be very much appreciated!

Thanks

[#3]

@Mosomiv 

You may use INDEX/MATCH https://www.excel-easy.com/examples/index-match.html to return lessons and their numbers based on teacher code and class number. If you provide sample file it'll be easier to illustrate on formula.

[#4]

@Sergei Baklan 

Thanks for your reply, I was thinking maybe Index and Match would do it, but I need to learn how to use them better.

My concern is if one teacher is teaching 2 year 7 Maths classes, how do i get both classes listed against their name? How do I do a formula that puts all of their classes across the row against their name?

I've attached the file for you to have a look at.

Thanks again for looking at this for me, much appreciated!

[#5]

@Mosomiv 

Thank you for the file. Could you please clarify how do you map 2021 classes on Class 1, Class 2, etc in Sheet1? Perhaps you may add one-two codes to the tables with manually added desired result.

Another question is on which version of Excel you are? Mainly is it support dynamic arrays or not, other words do you have functions as XLOOKUP, FILTER.

[#6]

@Sergei Baklan 

So what we normally do is manually enter, on "2021 Classes' sheet, the teacher code against a class, depending on who we want to teach it.

Then I copy all of those codes into one list (so instead of being multiple lists across the sheet (eg Year 7 classes, then Year 8 classes etc) they are all in one big list). I then sort them by the teacher code and copy across to sheet 1 and paste down the bottom somewhere, then transpose them, 1 teacher at a time, so they become horizontal lists for each teacher which I then copy and paste up the top of sheet 1 against the teacher code. This is just for the teacher code and class code. I also use vlookup to get the hours with the codes as well.

Very messy, hence my need for a better solution!

I'll repost the file with some example teacher codes on '2021 classes' and the desired effect on Sheet 1.

I have Excel for Office 365:

Thanks again for helping me!

I have