Forum Discussion

Mosomiv's avatar
Mosomiv
Copper Contributor
Aug 19, 2020

looking up across sheets?

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

excel
Formulas and Functions

5 Replies

  • Mosomiv's avatar
    Mosomiv
    Copper Contributor
    Aug 19, 2020

    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

     

      • Mosomiv's avatar
        Mosomiv
        Copper Contributor
        Aug 20, 2020

        SergeiBaklan 

        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!

         

Resources