Generate metrics for one vehicle when fleet, make or specific registration is selected

spikelovesmetal

New Member
Joined
Jul 1, 2015
Messages
7
Hi,

We've got a fleet of 58 vehicles that we used to manage by registration, so for example we would get individual requests per vehicle for MOTs, servicing, breakdowns, repairs etc. Every entry is tied to an individual vehicle, so there are hundreds of entries but it's fairly straightforward to filter on one vehicle and get all the information we need. We could also search a reference or description and see what vehicles it applied to, i.e. how many vehicles had to have bodywork repairs, or had punctures.

Our current tracker also generates Pivot metrics based on this as well, so you can filter by single or multiple vehicles and it'll tell you what the repair status is, how much it cost, whether it has to go back for more work etc. This comes up in a separate dashboard tab with a chart for each (status, cost, time in repair etc).

The reporting system has now changed so we no longer get individual requests per vehicle. We now have three categories to manage:
  • Fleet - every vehicle in the fleet, so for example inspections, servicing and MOTs come into this as a monthly, quarterly or yearly requirement
  • Model Specific - for example recalls, or when a set of vehicles reached end of life and are looking to be replaced
  • Vehicle Specific - a vehicle has a 'unique' problem, i.e. a breakdown, or someone crumples it on a bollard (again)
I've now split the tracker reference table so that it looks a bit like this. There are a lot more columns to the right on the real one with cost, time, recurrence etc but this is the main bit:

ABCDEFGHIJKL
Request TypeReferenceTitleDescriptionStatusAll VehiclesFordVauxhallOpelHondaMitsubishiVehicle Specific
Fleet WideFL-12345Racking InspectionQuarter 3 racking inspectionIn ProgressYes
(autofills G,H,I,J,K)
YYYYYAll
Model SpecificVA-12345Warranty - Headlight AlignmentWarranty headlight repairCompleteYY
Vehicle SpecificIN-12345Door RepairO/F door bodywork damagePendingYDV61SK

The biggest problem I have now is that I will soon have quite a few of these to manage once it rolls over to the new reporting system next month. Using the example above, although I can filter the table manually and (eventually) find out that vehicle DV61SK should have two requests by filtering the columns - one for racking inspection and one to fix the door - I don't know how to update my Pivot to allow me to one-click like I used to. I want to have it so that if I selected DV61SK in the Pivot, I would get:
  • All Fleet Wide issues
  • All Model Specific issues affecting Mitsubishi
    • Biggest problem currently is I don't know how to 'tell' it that DV61SK is a Mitsubishi
    • We also have some issues which affect multiple models, for example Vauxhall and Opel are essentially the same but with different badges on
  • All Vehicle Specific issues only affecting DV61SK
I have a table of registrations and models on a separate tab as a look-up. I also considered in Column L 'Vehicle Specific' just copying a list of the affected registrations in to replicate how it used to be set up, but with 58 vehicles it's time consuming and turns ugly very quickly.

Does anyone know how this can be done?

Thanks!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,825
Office Version
  1. 365
Platform
  1. Windows
Cross posted Generate metrics for one vehicle when fleet, make or specific registration is selected

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

spikelovesmetal

New Member
Joined
Jul 1, 2015
Messages
7
Cross posted Generate metrics for one vehicle when fleet, make or specific registration is selected

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hello,

This is the original post - I also asked ExcelForum later as I'd appreciate any help I can get. I have provided a note of the link (can't create links on that forum yet) on the ExcelForum post to this one (original post). I was worried because it's a tricky problem that has a deadline. I understand that replies take time on either site but I thought that asking lots of people might help to get an answer in time.

ExcelForum: Generate metrics for one vehicle when fleet, make or specific registration is selected

Sorry for the confusion :(
 

Watch MrExcel Video

Forum statistics

Threads
1,122,319
Messages
5,595,465
Members
413,992
Latest member
CSEGUIN1973

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