Creating individual timetables for our students.

%3CLINGO-SUB%20id%3D%22lingo-sub-2315815%22%20slang%3D%22en-US%22%3ECreating%20individual%20timetables%20for%20our%20students.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2315815%22%20slang%3D%22en-US%22%3E%3CP%3EI%20feel%20like%20this%20should%20be%20possible%20but%20I%20dont%20know%20where%20to%20start.%26nbsp%3B%20I%20would%20like%20to%20create%20individual%20timetables%20for%20our%20students%20from%20the%20spreadsheet%20Electives%20T2%20Demo.%26nbsp%3B%20I%20have%20attached%20both.%20I%20just%20cant%20work%20out%20the%20best%20way%20to%20have%20the%20Electives%20data%20sorted%20so%20that%20it%20can%20find%20what%20elective%20each%20student%20has%2C%20so%20then%20I%20can%20mail%20merge%20into%20the%20attached%20Demo%20T1%20document.%20I%20have%20created%20a%20second%20sheet%20in%20the%20Electives%20T2%20demo%20doc%20which%20I%20think%20is%20what%20it%20needs%20to%20look%20like%20for%20the%20mail%20merge.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2315815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2316014%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20individual%20timetables%20for%20our%20students.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2316014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043261%22%20target%3D%22_blank%22%3E%40wendy3280%3C%2FA%3E%26nbsp%3BOh%20my!%20Not%20the%20best%20way%20to%20collect%20data%20for%20time%20tables.%20But%2C%20with%20some%20clean-up%2C%20adjustments%20and%20several%20not%20very%20elegant%20links%20between%20two%20sheets%2C%20you%20could%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EClean-up%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EGot%20rid%20of%20all%20the%20merged%20cells.%20They%20just%20cause%20trouble%20when%20you%20try%20to%20reference%20a%20single%20column%20range%3B%3C%2FP%3E%3CP%3ESome%20of%20your%20names%20had%20trailing%20spaces%2C%20but%20not%20consistently.%20E.g.%20%22Ivan%20Cook%20%22%20and%20%22Ivan%20Cook%22.%20When%20Excel%20tries%20to%20match%20these%2C%20it%20can't%20as%20these%20are%20not%20the%20same.%20Fixed%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAdjustments%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EInserted%20Index%20columns%20in%20the%20MM%20Data%20sheet%20to%20determine%20on%20which%20row%20the%20student%20is%20found.Needed%20to%20pick%20the%20correct%20activity%20and%20teacher(s)%3B%3C%2FP%3E%3CP%3ERenamed%20the%20%22With%22%20columns%20in%20the%20MM%20Data%20sheet.%20When%20you%20do%20mail%20merge%2C%20you%20need%20to%20have%20unique%20column%20headers.%20So%2C%20I've%20used%20WithMO%2C%20WithTU%2C%20WithWE%20and%20WithTH.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFormulae%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CU%3EColumn%20B%3C%2FU%3E%20%26gt%3B%26gt%3B%20use%20MATCH%20to%20find%20the%20row%20number%20for%20the%20student%3B%3C%2FP%3E%3CP%3E%3CU%3EColumn%20C%3C%2FU%3E%20%26gt%3B%26gt%3B%20if%20the%20row%20number%20is%20greater%20than%2025%20then%20take%20cell%20A25%20from%20the%20T4%20schedule%20for%20the%20activity%2C%20otherwise%20A4%3B%3C%2FP%3E%3CP%3E%3CU%3EColumn%20D%3C%2FU%3E%20%26gt%3B%26gt%3B%26nbsp%3Bif%20the%20row%20number%20is%20greater%20than%2025%20then%20take%20cell%20A26%20from%20the%20T4%20schedule%20for%20the%20teacher(s)%2C%20otherwise%20A5%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20pattern%20repeats%20for%20each%20of%20the%20following%20days%2C%20though%20with%20shifted%20column%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20deliberately%20used%20direct%20references%2C%20so%20that%20you%20can%20see%20exactly%20which%20cell%20each%20formula%20links%20to%2C%20although%20it%20makes%20the%20formulae%20more%20difficult%20to%20read%20and%20maintain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20trust%20you%20can%20set-up%20the%20mail%20merge%20in%20word%20yourself.%20That%20will%20not%20be%20so%20difficult%2C%20now%20that%20you%20have%20a%20table%20with%20unique%20column%20headers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttaching%20a%20revised%20XLS%20file%20for%20your%20convenience.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.