Check value in the same cell of several worksheets - create timetable

pjmv · Apr 20, 2018

Hello,
I keep spending lots of time trying to solve this problem, and altrough not there I have learn some Excel in the way ... but not enough, so I hope someone can help me out..

Here it goes:

I have an Excel 2016 workbook with several worksheets with my School classes schedules/timetables. For each class a worksheet (F1,F2,F3, for example, are the name of the worksheets).
For each class period I need 3 lines: upper line name of class/discipline; middle line Name of teacher; bottom line Classroom
Example:
B3 Mathematics
B4 Paulo
B5 Class1
What I would want to do? To create the teachers timetable from the Classes timetable:
For this teacher I would create a worksheet for is time table and call it Paulo. I cell B4 Excel will search in all B4 cells of the students timetable and if it finds the name of the teacher fill the cell with the name of the worksheet (class name).
If it finds the name of the teacher copy the value in cells B3 and B5 to cells B3 and B5 of teacher worksheet.

Is this doable?

Been trying and trying and nothing seems to work

mumps · Apr 20, 2018

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

pjmv · Apr 20, 2018

OK here is the sample file
https://1drv.ms/x/s!All3rP64VLLfgYAVR7SWEYjnnoZpdQ

I have 3 classes F1 F2 and F3.
Something I need to say: each cell/time period of the timetable is composed of 2 merged collumns (in practical lesson the class is divided in two half classes)
In the worksheet of the teacher timetable (R_Sequeira) I need Excel to look in the same cell that is coloured in orange in the F1 F2 and Fs worksheets. If it finds the techer Name (R_Sequeira) fill the organge cell with the worksheet name (class name) and copy the value above to the cell above and the one under to the cell under that one (as in R_Sequeira(result) worksheet

Is this possible ?
Been doing this by hand and it takes days

mumps · Apr 20, 2018

Click here to download your file. I have added a sheet named "Faculty" that contains a list of your teachers. You can add names to it or delete names at any time. It is a named range. I have a sheet named "Timetable" with a drop down list in cell L2 that refers to the "Faculty" sheet. All you have to do is select a name from the drop down list and your teacher timetable will be created automatically. The macro assumes that the names of your teachers will always have an underscore (_) character in their names. The macro uses this character to distinguish between the class schedules and teacher schedules. Try it out and see if it works for you.

pjmv · Apr 20, 2018

Well this is great. I need to learn that VBA thing...

Just one thing: when creating the teacher timetable is it possible to place the name of the classes in the middle row? (where the teacher name is)

Discipline Identification
Class identification (and not teacher name)
Classroom Identiification

Thanks

mumps · Apr 20, 2018

Right click the "Timetable" tab and click 'View Code'. In the macro that appears on the right, replace this line of code:

Code:

ActiveSheet.Range(foundName.Address).Value = ws.Range(foundName.Address).Value

with this line:

Code:

ActiveSheet.Range(foundName.Address).Value = ws.Name

pjmv · Apr 20, 2018

Did as you said and it worked fine but just one more thing.

My school is a Vocational Agricultural School (www.epdra.pt). To adapt to the farm activities the timetables change every 4 weeks. (school year has around 34 weeks total).
I tried to add 3 more weeks bellow week 1 and now nothing works. Tryed to modify the cell range in the macro/VBA code but no luck
What I'm I doing wrong?

mumps · Apr 21, 2018

Could you upload a copy of the file that includes the four weeks in the timetable and also the four weeks in the class worksheets?

pjmv · Apr 21, 2018

Yes , of course. Here it is:
https://1drv.ms/x/s!All3rP64VLLfgYAWTX1p-MKY06njBg

In the real school year we'll have around 34 weeks of classes, adding down 4 weeks at a time. This consumes a lot of my time.
First I create the classes schedules (13 classes this year). This donne I fill the teachers schedulles. Some times I make a mistake (very frequently) of allocating the same teacher to more than one class. Alltrough this is possible (for examle the math teacher to give lesson to class F1 and F2 at the same time) excel should warn me with something like: This teacher is giving lesson to another class at this time period (8:30 to 9:30 for instance) do you want to advance?
But if this is to difficult just to fill the teachers schedule would be great. Please see the file

Thanks
Paulo

mumps · Apr 22, 2018

Copy/paste this macro in the code module for the "Timetable" worksheet:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Range("L2").Validation.Delete
    ActiveSheet.Name = Target
    Dim foundName As Range
    Dim sAddr As String
    Dim ws As Worksheet
    For Each ws In Sheets
        If InStr(ws.Name, "_") = 0 And ws.Name <> "Timetable" And ws.Name <> "Faculty" Then
            Set foundName = ws.Range("C5:L104").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundName Is Nothing Then
                sAddr = foundName.Address
                Do
                    ActiveSheet.Range(foundName.Address).Value = ws.Name
                    ActiveSheet.Range(foundName.Address).Offset(-1, 0).Value = ws.Range(foundName.Address).Offset(-1, 0).Value
                    ActiveSheet.Range(foundName.Address).Offset(1, 0).Value = ws.Range(foundName.Address).Offset(1, 0).Value
                    Set foundName = ws.Range("C6:L104").FindNext(foundName)
                Loop While foundName.Address <> sAddr
                sAddr = ""
            End If
        End If
    Next ws
    Application.EnableEvents = True
End Sub

This will create the timetables but unfortunately, it won't check for teaching conflicts.

Check value in the same cell of several worksheets - create timetable

pjmv

New Member

Excel Facts

mumps

Well-known Member

pjmv

New Member

mumps

Well-known Member

pjmv

New Member

mumps

Well-known Member

pjmv

New Member

mumps

Well-known Member

pjmv

New Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Check value in the same cell of several worksheets - create timetable

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock