Populating data from constantly changing Master workbook

Hi all,

I'm new here, and have posted this thread into the Excel General section as I am not sure which section would best fit what I am trying to achieve.

I have an Excel workbook Master that I want create new, constantly updating/refreshing series of workbooks from. The Master data sheet changes monthly as new student lines are added, so the data source would be changing regularly.

There are over a 100 columns of data and about 200 rows in the Master. I only need about some of the columns in my new workbooks.

An example of one workbook would be that I have the existing four columns taken from the Master:

Student Number | Student Name | Confirmation Date | Supervisor 1 | Supervisor 2 | Comments
1234567 | Joe Smith | 12/12/2018 | Susan Jones | John Doe | XXXXX

When new students get added, I want them to populate underneath the existing data. Additionally, if Joe's confirmation date or supervisors change, I also want that information to refresh from the new Master, whilst retaining any comments I have added about the student.

I hope I've provided enough information. The current tracking system is fully manual which is onerous and unreliable, and I am sure that something like this is possible, but I don't have enough Excel knowledge to make it happen. Any assistance that is provided would be greatly appreciated :-)

Hi FDibbins,

Thanks for the advice. I have uploaded a dummy workbook with two sheets: Sheet 1 is the Master data and Sheet 2 is what I want to use from the Master Data in a new workbook, in addition to adding some of my own extra columns.

If you need any further info, please let me know.

Now I'll apologise for my late reply. I thought I would receive an email notification of your response, but I never did!

Ideally, I would like to take the data from Sheet 1 and have only certain columns of data populate into a new workbook altogether. I would like to be able to refresh the new workbook from Sheet 1 data, which is constantly changing.

Hope this makes sense. Wish I had more Excel wits about me!

You have to tell us what criteria are to be used to determine if a record should appear in Sheet2 (or in Sheet3, Sheet4 etc.). It might be that you want to select based on Supervisor, in which case you would have subsidiary sheets one for each of your supervisors. Or it might be based on Course, so you would have sheets for each course, and so on.

Rather than have multiple sheets of the same layout, I prefer to suggest that you have one, but in that you could choose the name of the Supervisor from a drop-down list, and have the data presented just for that supervisor. Choose a different supervisor, and the display changes accordingly. Of course, this approach is not really suitable if you want to add other data onto the subsidiary sheets.

Whichever way is chosen, you can achieve it with just a few well-chosen formulae, so the data is always up-to-date in the subsidiary sheet(s).

Your sample file only showed one record, so it is difficult to deduce exactly what you want to achieve. It would be better to have a few more example records in your Master sheet (no more than about 20), and you need to tell us what selection criteria is to be applied.

Hope this helps.

Pete