Converting Data Between Multiple Spreadsheets into 1 Master Spreadsheet

[dibianst]

Hi,

At work I was just put in charge of sending the employee production report to management. Right now they have a Master Spreadsheet I use which list the Date, employee name, supervisors, manager, hours worked and production for that day. I receive approximately 60 different emails with spreadsheets from employees with their production information for that day. I take their production details and input it into the Master spreadsheet which I send out. This is surprisingly very time consuming. My question is would there be a way to get my master spreadsheet to automatically update as employee enter their production information on their individual spreadsheet. I know how to get data from one workbook to show up on a different workbook lets says a master workbook by going to the View tab and then the switching windows button. I'm just having difficulty wrapping my head around how I would do this if employees all have their spreadsheets saved in their personal drives. All employees use the sample individual spreadsheet. So, all the spreadsheet I receive are identical with he exception of the production data and employee name.

 

[Smallman]

Hi dibianst

Do you have a dummy version of the file you want updated. If you are not comfortable using employee names put a example names in and example data. Make the format the same though. That will help everyone understand your problem.

Take care

Smallman

 

[dibianst]

I have attached a basic sample spreadsheets to help illustrate what I am talking about. So the spreadsheet titled Dummy Version Employee would be an example of what I receive from each of the 60+ employee. What I am doing now is taking the information in each spreadsheet I receive from Each employee and I manually input it into the Master Spreadsheet. I"m just trying to figure is there any way I can avoid having to manually open 60+ different spreadsheets from the 60+ employee I am tracking and manually input the data into the master spreadsheet. Its just taking way too long. I know how I can link in this example the dummy version spreadsheet to the master spreadsheet. So there comes the confusion. I was thinking I could take the spreadsheet I receive from the employees, save it and then link it to the master spreadsheet. This would work and would transfer the data over but I wouldn't want to do this every day. So, I want to set up a process where I can have each of the employees enter their production data into their own personal excel production report. Then as soon as they save the information to have it set-up where it will update on my master spreadsheet. This way I can stop having to have each employee individual send me their log and to manually enter the information, it would just automatically be updated. Hope this makes sense. I"m lost

 

[Smallman]

Hi dibianst

I am confused. You said all your templates were the same but your master sheet does not look like you template sheet. The parent and child should be identical.

Firstly clear that up because in my mind the Master workbook is exactly the same as the template.

Secondly do all of your employees have access to a shared drive? If I were running this process I would have everyone save their file onto a drive then I would consolidate all of the data from the drive to the master workbook. If the upload was done daily I would have a drive dated and a drop down in the master workbook with exact date names. After I chose a date I would run my process to upload all 60 files into 1 sheet of the Master workbook. I would then save the Master as MasterTodaysDate and you are done.

Take care

Smallman

 

[jeffreyweir]

@dibianst: I don't think it matters so much if your master differs from your templates - just so long as all the templates are identical and you let us know how the 'Supervisor' and 'Manager' columns in the master will be populated.

When I do this kind of thing, my master and templates are almost never identical...I often have additional columns in my master spreadsheets that record details about where the data came from - e.g. sometimes I'll record the source template's workbook name or something. And sometimes I don't need all the info contained in the templates.

Perhaps that information will be contained within the files that you'll be amalgamating? If so, that's a handy way to record in your master file which info came from where.

Or perhaps you'll just be doing a VLOOKUP using a table stored in your master?

 

[dibianst]

JeffreyWeir: Thank you. What I'm trying to do is find a way so I can have a Masterspreadsheet with all employees production information to be auto-updated. I think I might be confusing everyone, it would be fine for me to propose a change to the report but basically the report has all the employees in the department, whom the employee supervisor is in the cell next to the employee name and then who their manager is in the cell next to the supervisor name. Then next to manager name cell I would have start with their production information, task completed, pended tasks etc. I'm trying to see if there is a way that employees can fill out their production template which would be the same for everyone and then have that information tied to my masterspreadsheet so the master spreadsheet would be automatically update when the employees update their individual sheet. I just don't know if this can be done if the masterspreadsheet is stored in my individual drive and each employee saves their individual production in their individual drives.

 

[jeffreyweir]

The report has all the employees in the department, whom the employee supervisor is in the cell next to the employee name and then who their manager is in the cell next to the supervisor name.

Your sample template does not have manager name or supervisor name in it. But your master template does. Can you please explain how you will populate the Manager Name and Supervisor Name fields in the Master?

I just don't know if this can be done if the masterspreadsheet is stored in my individual drive and each employee saves their individual production in their individual drives.

The files all need to be stored somewhere that you can access them. So if there is not a common drive that everyone uses, then all the files will have to continue to be emailed to you and then you will have to store them in a common folder in your workbook. But once they are stored in the same place, then you can use a macro to update your master file.

Here is an example. http://blog.contextures.com/archive...vot-table-or-excel-table-from-multiple-files/

It would need some slight modification to suit your needs, because as Smallman points out your template and your master are not identical.

 

[dibianst]

I understand. jeffreyweir I wanted to see if you could take a look at what I have attached and point me in the right direction. I attached what would be my individual production log and the master employee production log. I'd like it so that all spreadsheets are on a shared folder I update my spreadsheet and the master spreadsheet automatically updates with the data I entered in my individual spreadsheet. I've been running into problems. I'm new to macro's.

I linked my spreadsheet and this spreadsheet together by putting an = sign in the row with my name and in the first cell I want to capture the data from my individual spreadsheet I then went to 1. review, 2 switch window tab and then I selected the spreadsheet I wanted to link and I selected the cell I wanted to link. Am I doing this correct? It just seems odd I am unable to link more than one cell at a time, also when I added my macro it just repeated what I did in the first cell. I'm really trying my best to understand the least time consuming and most simplistic way for me to program 100+ individual employee production spreadsheets just like mine (with the exception of the employee name will be different and there will be different supervisor for some employees) to have the data feed into the master spreadsheet as its updated. I know there is a simpler way to do this I just have been unable to firgure it out.

 

[jeffreyweir]

I attached what would be my individual production log and the master employee production log.

Your 'Daily Production Log' template does not have a 'Team' column in it, but your master template does. Can you please explain how you will populate the 'Team' fields in the Master? Where will you get the 'Team' from in the 'Daily Production Log' template?

I'd like it so that all spreadsheets are on a shared folder I update my spreadsheet and the master spreadsheet automatically updates with the data I entered in my individual spreadsheet.

I linked my spreadsheet and this spreadsheet together by putting an = sign in the row with my name and in the first cell I want to capture the data from my individual spreadsheet I then went to 1. review, 2 switch window tab and then I selected the spreadsheet I wanted to link and I selected the cell I wanted to link. Am I doing this correct?

I don't think you quite understand my proposed solution. You initially said:

I receive approximately 60 different emails with spreadsheets from employees with their production information for that day. I take their production details and input it into the Master spreadsheet which I send out.

The solution I propose is that when you get the emails with the templates attached, you save the templates to a single folder in your workbook, and then trigger a macro to consolidate them all in one go to your master file. And the example link http://blog.contextures.com/archive...vot-table-or-excel-table-from-multiple-files/ shows the approach I'm suggesting that I think will solve your problem. Can you please look at that link, download the sample file, have a play with it so you can then see why I think this is applicable to your request.

If this approach is satisfactory, then I or someone else here can help you make the necessary changes to the code. We don't need you to link anything, we don't need you to record any macros. I just need you to read carefully what I've written above and answer the above questions. Otherwise I can't help you out.

I don't have a lot of time to help you out with this. I need you to read carefully over my suggestions.

 

[dibianst]

our 'Daily Production Log' template does not have a 'Team' column in it, but your master template does. Can you please explain how you will populate the 'Team' fields in the Master? Where will you get the 'Team' from in the 'Daily Production Log' template?

I see what you mean I added the'team' column to my 'daily production log'. What I do now is I just copy and based all the date from the previous day change the date, delete the data and add the new days production data. I do that same process each day i record the data and it seems to be working with calculate the percentages and such correctly.

I followed the link you provide and grabbed the excel spreadsheet with the marco in it. I think I am close to the finish line but it doesn't look quite right yet. For some reason after I use the macro to take the data and have it transferred to the master spreadsheet its adding duplicate lines. Plus I"m looking at the link and trying to understand how to create the macro to begin with. So for my master spreadsheet I'm trying to understand how to create the code to have the macro appear on my masterspreadsheet. Also using this macro would I be able to transfer multiple spreadsheets at once. So, If I wanted to add all spreadsheets for supervisor Ronda at once so that I have all the data for one sup grouped together rather than having one row for supervisor ronda and the next for supervisor Claudia etc. If this is taking up too much of your time I understand.