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...
excel
Formulas and Functions
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!

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 20, 2020

      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.

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

        SergeiBaklan 

        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

Resources