How to Connect Employees' Individual Excel Workbooks to a Main Workbook for Automatic Data Updates?

[kaizoku99]

Hey everyone,

I need some guidance on how to efficiently connect multiple employees' Excel workbooks to a main workbook to streamline the process of updating biweekly hours. Currently, I have separate Excel workbooks for each employee, and within them are worksheets with their respective biweekly hours. On the other hand, I maintain a main Excel workbook that lists all the employees and their hours for the entire month, with a total of 12 worksheets for each month.

I want to create a system where employees can enter their hours on their individual workbooks, and those updates are automatically reflected in the main workbook, saving time and ensuring accurate data.

Here are the specifics of what I'm looking to achieve:

Each employee has their own Excel workbook containing a worksheet for biweekly hours.

The main Excel workbook has separate worksheets for each month, totaling 12 months.

When an employee update their biweekly hours on their individual workbook, the corresponding cells in the main workbook should be automatically updated without any manual intervention.

i don't want it to create a new sheet I just want for example when I enter the hour in the first of January column and regular hours row to also be updated in the same cell that corresponds to it in the monthly workbook.

I believe there might be different approaches to achieve this, but I'm not quite sure about the best way to proceed. If anyone has experience with connecting workbooks or using Excel functions, I'd greatly appreciate your advice and guidance.

Possible solutions that come to mind include using VBA macros, linking cells across workbooks, or even exploring external plugins. I'm open to any suggestions or step-by-step instructions you might have, especially if you've tackled a similar issue before.

Thank you all in advance for your help!

 

[OaklandJim]

This all sounds doable. But here is food-for-thought...

Having a bunch of links to other workbooks is doable but in my experience, such links are not as good as one would hope. Plus, it sounds like you'd have a bunch of links to add 26 biweekly periods for a given year for each employee, which seems like a big hassle.

One complicating factor -- especially for links -- is that a given biweekly period might have days in two different months. And, without thinking it through, I believe that a biweekly period might span two different years. A solution would have to accommodate those considerations.

Aside from the effort required, a key issue is that someone willing to help would have to create your workbooks -- employee-specific and roll-up -- to develop a VBA solution. That is unlikely.

So, if I were to assist I'd need some workbooks to work with which have realistic-but-fake data. I do realize that coming up with so much fake data would take a while but if you get a good solution then it may be worth the effort. (One possibility is to provide real data from real employee-specific workbooks but with fake names.)

The general approach I'd try to implement is to develop vba that copies month-specific values for each employee to the roll-up workbook for each month. I imagine a roll-up workbook that has all employee names which would be used by vba code to locate each employee-specific workbook whose name includes the employee name (or ID number). So for the name Bill Ding in the roll-up workbook there would be a workbook named something like Bill Ding 2023.xlsx

I hope that helps.

 

[OaklandJim]

Put the files on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the Mr Excel message area. Make sure that other people can access the files!

 

[kaizoku99]

Hey thanks for your advice, Unfortunately, I had to link them manually as I couldn't figure out how to create a VBA script for this

 

[OaklandJim]

Like I said, I'm willing to help with a vba solution, if that is what you are after. But if links work then use that approach.