Automation using a macro or formulas for management reporting

[excelvba123]

Hello Seniors,

Hope you are all well. (Sorry to be overdescriptive)

I work for a car manufacturer and we source parts from different suppliers across the globe.
The attached sheet provides information about all our suppliers requiring 15 shift deviations. 15 shift deviation is anything above the normal 5 day a week (@ 3 shifts of 8 hrs each per day in a 24 hr cycle) so 5 days x 3 shifts x 8 hours each = 15 shifts per week which is a normal shift pattern. If the demand for car parts increases then the supplier has to work more than 15 shifts that is may be 6 days a week or even 7 days a week. So they can increase their working to 16, 17, 18, 19, 20 0r 21 shifts per week. Maximum a supplier can work is = 7 days x 3 shifts (8 hrs each) = 21 shifts. In order to get these working shifts they need approvals from us because we pay for the extra working hours. Some requests are approved, some are rejected, some are just awaiting approvals or some our approved but now expired. So basically the attached tracker gives details about all deviation requests and their respective status. So far I have been doing the reporting manually and the same I have attached on the worksheet named metric new. I want to request you to please find a way to automate this task or some kind of dashboard to tell to the senior management that this the position of our 15 shift deviation tracker. In meetings the senior management only want to see the highlights and not the whole tracker so please help me in doing the automation part for this job. I have used dummy company names for confidentiality reasons.


I have included the metrics on a separate tab(we do the metrics manually and that is what I am looking to automate)
The important columns in our data set on our tracker are as follows:

1. CoC (is centre of excellence- which Department the part belongs to)- Column AK
2. Company Name - Column G
3. Category - Column C
4. Shift Deviation Requested - Column H
5. Plan to return to 15 shifts - Column K
6. Component - Column Q
7.Start Date of 15 shift deviation - Column R
8.Approval Requested Until - S

My director would like to see the following:
A. Count of Jobs :All APPROVED Deviations Requests by CoC's under the following categories:
Plan back to 15 shifts Required - Those suppliers which has "N" under Column H
Expired- Where the dates are expired. Any job with date which is prior to current date in Column S
No end Date - Any jobs in Column S which says " No end Date" meaning supplier will keep working until further notice.
All other jobs - All other jobs which are still valid and not expired as per the column "S".
and then the total of everything (both by rows and columns) as you can see in the worksheet "Metrics New"- table.

B. Count of Jobs- All open Deviations(not approved/rejected etc) Requests: Count of all open deviations (which are NOT APPROVED) under the following categories:
Awaiting Approvals - Under the Category Column "C"
Rejected - Same under Column "C"
Continental Work Pattern - Same under Column "C" some suppliers work permanent >than 15 shifts per week and we call them Continental working pattern. These are also usually with no end date.
and then lastly total of all jobs by rows/columns.


C. Lastly I write all the company names(Tracker Column G) manually at the bottom of the table under the respective columns in the "metric new" worksheet.

D. Then lastly the GRAND TOTAL of everything in Column N on the "Metric New" worksheet.

Please note “Engines” category is not usually a part of the metrics and therefore we always exclude engine jobs in the metrics. So if the total lines on our tracker are added without the engine jobs (6) the total comes to 75 and that is just equal to our metrics total of 75.
Any help would be a boon for me and I will be very grateful to you as always. I know this place has got some genius talents so please come forward and help.
Best Wishes
Amit