Jun 10 2021 11:41 AM
Sorry, but I'm a newbie looking for guidance, I want to learn. I have two workbooks. For now I figured out the first one and have it working well for my purposes. Very simple formulas to calculate time elapsed per trip and daily totals. Still copying and pasting from the source, but that's minimal. I build one worksheet per day, and start a new workbook every month, to avoid the massive number of tabs on the workbook. In every worksheet I monitor about 10 to 15 individual's times and the number of trips they run per day. The number of individuals fluctuates with their schedule, that's why I'm still copying and pasting the info from the source.
To avoid having to go back to specific dates and manually adding information, I'm trying to build a second workbook (with one worksheet per individual) that would report back on a specific week or month, giving me a specific individual totals. I meddled with =IF(HLOOKUP), but wasn't able to set the parameters to look at every sheet from the first workbook, selecting one specific individual, and getting his totals for the week/month. For practice, I was able to link specific cells across the two workbooks and get them to synchronize data, but that's not enough. I need the 2nd workbook to get information from different cells, from all the worksheets on the 1st, on specific individuals, and placing it on each individuals own sheet.
Any guidance appreciated. Win 10 - Excel 2013.
Jun 10 2021 03:02 PM
Jun 17 2021 09:23 AM
Jun 20 2021 12:48 AM - edited Jun 20 2021 01:07 AM
SolutionHi @eschalm,
Maybe you can use this:
Sheet where you track runs:
change the names and replace the hours with your actual hours
Sheet where you view the summary (Dynamic Power Pivot, updates automatically) from the data you enter in the entry sheet by pressing ctrl+alt+F5 to refresh
You can view Daily, Monthly, and Yearly
You can change the dates in the entry screen within the table and it will update the summary pivot table view and replace the hours, with your hours and it will update the pivot view by pressing ctrl+alt+F5
i used a formula for the hours to generate random numbers so you can just remove the numbers and replace it with your numbers as well as the employee name and since it is defined as an excel table it will dynamically shrink and expand as you add employees so if you delete employee names the table will shrink.
Also make sure to change the names in the employees table:
The tabs in red are the automation, do not mess with those sheets!!!!
file is attached, play with it and see if you can use it.
cheers
Jun 25 2021 01:22 PM
@Yea_So Thank you very much. I'll definitely play around with it.
Jun 20 2021 12:48 AM - edited Jun 20 2021 01:07 AM
SolutionHi @eschalm,
Maybe you can use this:
Sheet where you track runs:
change the names and replace the hours with your actual hours
Sheet where you view the summary (Dynamic Power Pivot, updates automatically) from the data you enter in the entry sheet by pressing ctrl+alt+F5 to refresh
You can view Daily, Monthly, and Yearly
You can change the dates in the entry screen within the table and it will update the summary pivot table view and replace the hours, with your hours and it will update the pivot view by pressing ctrl+alt+F5
i used a formula for the hours to generate random numbers so you can just remove the numbers and replace it with your numbers as well as the employee name and since it is defined as an excel table it will dynamically shrink and expand as you add employees so if you delete employee names the table will shrink.
Also make sure to change the names in the employees table:
The tabs in red are the automation, do not mess with those sheets!!!!
file is attached, play with it and see if you can use it.
cheers