Automatic staff to student timetable - Is this possible?

hb109420

New Member
Joined
Mar 11, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,
I work for a hospital school teaching students who are too ill, physically or mentally to go into school. Our remote teaching team is continually growing and we're trying to create a better system of centeralising staff and student timetables.

I was wondering if someone could tell me if the following is possible and what functions I need to learn in order to make it work......

I'd like to complete the top table 'staff timetable', by imputting students initials and the bottom 'student timetable' to automatically update with the staff initial. I've tried using the lookup function but found it didn't identify if a student was double booked it just returns the last teachers initials. There are also occasions where small groups are taught and I couldnt get the lookup function to find TG (for example) if the cell had TG & TS.

I'm a complete newbie so any help would be greatly appreciated!
Thanks
 

Attachments

  • image.png
    image.png
    17.9 KB · Views: 14

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi, try:

B21 (copied down and across) =IFERROR(INDEX($A$3:$A$17,MATCH($A21,B$3:B$17,0),1),"")
 
Upvote 0
Hi, try:

B21 (copied down and across) =IFERROR(INDEX($A$3:$A$17,MATCH($A21,B$3:B$17,0),1),"")
Thanks for replying so quickly!
I really appreciate you taking the time to think about the actual formula I needed to use.
This still gives me the problem of not identifying if two members of staff are down to teach the same student at the same time (This isnt possible and I need to highlight the clash in some way) or not identifying a student if on the staff timetabel its down as a group ( Two students initials in the same cell)
 
Upvote 0
For the first issue, you could use conditional formatting>highlight cell rules>duplicate values.

Just use it on the first column, then double click format painter to paste it on the others
 
Upvote 0
For the first issue, you could use conditional formatting>highlight cell rules>duplicate values.

Just use it on the first column, then double click format painter to paste it on the others
Amazing! Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top