Training records

Topic

New Reply

Win 7
MSO 2010

I wish to establish a Master training record for our staff of 45.

My intention is to have individuals maintain their own records but these are to be uploaded to a master file on save. BTW all files are stored on the network

Is this the best way or should I just have the master update whenever it is opened?

Reply | Quote

Replies

Phil,

Go with number 2. I built a DB for a training org. while employed that used excel. Each group maintained a DB of their courses and the cost/fee information. When the overall view was necessary the master workbook would be opened and an Auto_Open routine would clear the existing data and reload from each group workbook. It worked like a charm. Keep in mind that all the workbooks had exactly the same structure and macros/VBA the master upload was controlled by the UserId of the main finance personnel so even though the code was in each workbook only the appropriate people could execute it using the combined workbook. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

It’s amazing all you can do with Excel. And Excel is easy to deal with.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

Jim hi
OK any suggestions. I am at a loss.

I have determined the best way would be to use SharePoint but my management probably won’t agree to that.

Reply | Quote

RG is the expert here, but let me take a stab at it.

I did a similar thing many years ago. Basically, you have one master workbook and several “dependant” workbooks. Each dependant workbook is linked back to the master workbook by the formulas that you have in the cells of the master workbook — these formulas look to the dependant workbooks for their data.

The users will each have access to the appropriate dependant workbook. When they make changes to their particular workbook, the formulas contained in the cells of the master workbook will automatically get the latest data from the appropriate dependant workbooks (the ones specified in their formulas). The way the formulas get the info is that the cells of the master workbook are “refreshed” or updated, so that they will contain the latest information.

The easy way to refresh an Excel workbook is simply to open it. I’m sure, tho, that you can also refresh it while it’s open. (I believe that you “select all” and then hit F5.)

Until it is refreshed, the master workbook will still contain the data it got from the previous refresh.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

Thanks for that Jim
I think what I can’t get my head around is that it is appended information, not from a specific cell, that I would need to refresh.

We have 45 staff so the master workbook would need at least that many worksheets. I could develop some VBA code to copy the individual worksheets but not sure how long this would take to run.

Extrapolating that, I guess I could also develop code for “On Save” copy sheet and paste to corresponding worksheet in master workbook.

Hmmmmm!

Reply | Quote

Phil,

Rather than have 45 worksheets just have one master DB worksheet. Set each of the 45 individual workbooks to include the name of the person in each record then when you copy them to the master just copy the next one where the last one left off. Then you can use Auto filter or Advanced filters to slice and dice the information or even pivot tables for another view of the data. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG Thanks for that
I am not sure I understand and I don’t believe my Excel skills are up to slicing and dicing!

We need to produce individual records for each staff member to show they have had requisite training in the previous period

Reply | Quote