May 02 2021 07:08 PM
May 02 2021 07:08 PM
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.
May 02 2021 09:49 PM
@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.
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.
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.
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.