Creating individual timetables for our students.

Copper Contributor

I feel like this should be possible but I dont know where to start.  I would like to create individual timetables for our students from the spreadsheet Electives T2 Demo.  I have attached both. I just cant work out the best way to have the Electives data sorted so that it can find what elective each student has, so then I can mail merge into the attached Demo T1 document. I have created a second sheet in the Electives T2 demo doc which I think is what it needs to look like for the mail merge.

1 Reply

@wendy3280 Oh my! Not the best way to collect data for time tables. But, with some clean-up, adjustments and several not very elegant links between two sheets, you could get it to work.

 

Clean-up:

Got rid of all the merged cells. They just cause trouble when you try to reference a single column range;

Some of your names had trailing spaces, but not consistently. E.g. "Ivan Cook " and "Ivan Cook". When Excel tries to match these, it can't as these are not the same. Fixed that.

 

Adjustments:

Inserted Index columns in the MM Data sheet to determine on which row the student is found.Needed to pick the correct activity and teacher(s);

Renamed the "With" columns in the MM Data sheet. When you do mail merge, you need to have unique column headers. So, I've used WithMO, WithTU, WithWE and WithTH.

 

Formulae:

Column B >> use MATCH to find the row number for the student;

Column C >> if the row number is greater than 25 then take cell A25 from the T4 schedule for the activity, otherwise A4;

Column D >> if the row number is greater than 25 then take cell A26 from the T4 schedule for the teacher(s), otherwise A5;

 

This pattern repeats for each of the following days, though with shifted column references.

 

Now, I deliberately used direct references, so that you can see exactly which cell each formula links to, although it makes the formulae more difficult to read and maintain.

 

I trust you can set-up the mail merge in word yourself. That will not be so difficult, now that you have a table with unique column headers.

 

Attaching a revised XLS file for your convenience.