Summarising data on multiple worksheets and work books in close to real time

Gareth246

New Member
Joined
Aug 20, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
We have a workshop and each of our technicians have a excel document they open.

On the workbook there are 6 tabs which are for 6 different jobs that they may be asked to do today. An example is, fit number plates or quality check.

They scan our job card at the beginning of their job and at the end of each job.

A typical entry would look like;

Person name, scanned vin, time/date stamped, scanned vin (for finishing job), time stamp, then a date stamp

I have 20 colleagues each using their own workbook, at the moment I am running power query for each of the tabs, or ‘jobs’

From that I can drill in to individual performance which is great.

But as these workbooks are shared and updated at 5 minutes Intervals I’m trying to automate a larger top view automatically. What I need to see is;

Each of the 6 jobs with a current average time per job Live-ish.

underneath each job a breakdown of the colleagues name that is doing that job today or between a set of dates and their average time.

To summarise I want to see if we are running behind on a particular job such as quality control in real time and then be able to see who is doing that task and maybe holding up the average time.

What would be the best way to do this? Could this be a power bi type thing?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top