SINCRONYZING DATA ACROSS WORKBOOKS/WORKSHEETS

[eschalm]

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.

[#2]

Hi,

Yes this is possible. However, it would involved building a pretty complicated power query data model. You may want to reassess how the original source data is recorded. If it was all saved in one table (with one column for date) it would be easier to pull than many separate worksheets.

regards,
StoneKiwi

[#3]

Thank you for your response. I got so busy that I couldn't come back for a little while. So, I'll try to see if I find another way to create the original table. Regardless, I looked a little into the power query option and I liked it too. I'll poke around to see what I can do. Thank you again.

[#4 · Accepted solution]

Hi @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

[#5]

@Yea_So  Thank you very much. I'll definitely play around with it.