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

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 like the example below. There are a lot more columns to the right on the real one with cost, time, recurrence etc but this is the main bit:

Capture.JPG

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!

Edit: I also asked users in a different community at Mr Excel (apologies - can't post links) as it's a tricky problem with a time limit and I'd appreciate any help I can get. Sorry for the confusion!

mrexcel.com/board/threads/generate-metrics-for-one-vehicle-when-fleet-make-or-specific-registration-is-selected.1128688

Administrative Note:

Welcome to the forum.

We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s).

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

https://www.mrexcel.com/board/thread...ected.1128688/

(Note: this requirement is not optional. No help to be offered until the link is provided.)