Taking data from an MIS, pasting into excel and creating a readable re-arranged sheet

[SueBristow]

Hi everyone

I am trying to automate something that we do quite regularly when teachers leave and the timetable needs an edit. I export a file from our timetable software showing all the teachers that we may use in re-arranging the timetables minimising the use of a supply teacher.

The file comes out of the software (attached sheet in workbook Raw Data) with the teacher code in column A, with the teaching periods for the full two week timetable across the columns.

I have also created another sheet (TT export from SIMS) where I have tried pasting the raw data into a table.

What I want to achieve is paste this data into a spreadsheet and the nicely formatted printout would appear on another sheet (reformatted timetable) with each day as a separate table. This sheet currently has only Mon and Tues showing but I have put both week 1 and week 2 of these days.

I have tried using Tables (these are new to me so I'm not sure how they work), so that I can delete the previous data by selecting a cell other that the header, Ctrl + A and delete rows, then paste the new data in creating a table of the right size.

I would also like the re-formatted tables to expand and contract with the source data.

I suspect I am asking too much and I need to put this into a database but am ever hopeful that it can be achieved without this and without the use of code.

I have tried using a word mail merge (there is no calculation to be done so a word document as output would be fine) but couldn't make it work.

I also have a problem with the two Tuesday tables as I just cannot make the table formula look right - I am expecting the formula to be =Table1[@[1Tue:1]] but this gives an #value error when I type it but when I try selecting the cells, the formula shows as ='TT Export from SIMS'!M2 and when I update the data in TT Export from SIMS I get errors - the cells also do not autofill.

The end result would save me about 30 minutes of time each time this happens so it's not a mega issue but it is 30 minutes of boring copying and pasting.

Many thanks as always for your help.

Sue