Hi There,
I have a file template with a series of identical tabs containing tables as individual timesheets for various employees. I make one new file each week. Each employee has their own tab and different weeks we may have a new/different mix of employees.
I need to take all the tabs that exist in one week and merge the table data from each tab into a single summary tab (within the same file) - so that each line of data is listed in a single table. I can then use this summarised data to create a pivot table to extract hours spent on various projects and overall hours for the week for each employee.
All the tabs are an identical layout containing tables, they are filled out by the employees using drop down menus. Different employees may populate more or less rows in their tables depending how many projects they work on in a week. Most importantly, as these files are created by the users, I need a solution that can be set up and locked down so there is no need for complex processes each week. Someone can refresh the pivot table and the updated info is presented.
Biggest hurdles:
The lists change length from employee to employee.
The number of tabs change from week to week depending on the staff working.
Each tab needs to remain as single timesheet page that can be printed out and passed on
I have had little success:
I have used RDB-Merge but this is a manual process and produces a messy file that needs to be cleaned.
I have investigated Power Query - but this is not included with excel mac
I have tried a pivot table with multiple data sources but this function has been dis-continued in latest excel mac
i have tried consolidate function but this is summarizing tables not merging tables
While I'm fairly confident with the front end of excel, I have yet to delve into scripts - so please bear that in mind - However after my arduous search I feel some form of scripting is my only pathway left (?) If there is a way to do this without script, that would BLOW MY MIND!! If not - then the simpler the script the better!
I feel I've really reached the annoying limits to excel mac - hopefully I'm wrong. But this is the system we use, so please, the advice: "use the windows version" is not really relevant. If I could I would have been doing so months ago.
Any questions or further info - let me know - I have attached a link to the draft file below - hopefully it works!
Much Thanks for any advice or direction... or a solution!!
Cheers
Ben
https://drive.google.com/open?id=1TNX93GODMVS8ipJcBnhGIbzKSAhI1WI2
I have a file template with a series of identical tabs containing tables as individual timesheets for various employees. I make one new file each week. Each employee has their own tab and different weeks we may have a new/different mix of employees.
I need to take all the tabs that exist in one week and merge the table data from each tab into a single summary tab (within the same file) - so that each line of data is listed in a single table. I can then use this summarised data to create a pivot table to extract hours spent on various projects and overall hours for the week for each employee.
All the tabs are an identical layout containing tables, they are filled out by the employees using drop down menus. Different employees may populate more or less rows in their tables depending how many projects they work on in a week. Most importantly, as these files are created by the users, I need a solution that can be set up and locked down so there is no need for complex processes each week. Someone can refresh the pivot table and the updated info is presented.
Biggest hurdles:
The lists change length from employee to employee.
The number of tabs change from week to week depending on the staff working.
Each tab needs to remain as single timesheet page that can be printed out and passed on
I have had little success:
I have used RDB-Merge but this is a manual process and produces a messy file that needs to be cleaned.
I have investigated Power Query - but this is not included with excel mac
I have tried a pivot table with multiple data sources but this function has been dis-continued in latest excel mac
i have tried consolidate function but this is summarizing tables not merging tables
While I'm fairly confident with the front end of excel, I have yet to delve into scripts - so please bear that in mind - However after my arduous search I feel some form of scripting is my only pathway left (?) If there is a way to do this without script, that would BLOW MY MIND!! If not - then the simpler the script the better!
I feel I've really reached the annoying limits to excel mac - hopefully I'm wrong. But this is the system we use, so please, the advice: "use the windows version" is not really relevant. If I could I would have been doing so months ago.
Any questions or further info - let me know - I have attached a link to the draft file below - hopefully it works!
Much Thanks for any advice or direction... or a solution!!
Cheers
Ben
https://drive.google.com/open?id=1TNX93GODMVS8ipJcBnhGIbzKSAhI1WI2
