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.
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.