Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Training records

    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?
    cheers

    Phil Carter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 406 Times in 378 Posts
    It's amazing all you can do with Excel. And Excel is easy to deal with.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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.
    cheers

    Phil Carter

  5. #5
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 406 Times in 378 Posts
    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.
    Last edited by mrjimphelps; 2013-07-17 at 17:37.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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!
    cheers

    Phil Carter

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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
    cheers

    Phil Carter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •