+ Reply to Thread
Results 1 to 10 of 10

Vehicle Maintenance Forecast help please!

  1. #1
    Registered User
    Join Date
    03-30-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    9

    Vehicle Maintenance Forecast help please!

    Evening again....

    Please see attached work book.

    I manage a varried vehicle fleet with different maintenance events at different times that's managed by a 3rd party application.

    I run a report on that application and export the results into excel, copy and past onto my 3rd party Information Tab.

    On my overview tab, i'm trying to combine similar maintenance events and timescales and auto populate the forecast.

    I'm ok with index and match, but i have to manually select the relevant maintenace event to each vehicle(which defeats the purpose of trying to simplify things).

    Question is: a) How do I match Overview Collumn B to 3rd Party Information Collumn A, and match each relevant maintenace event (Overview F4:N11) to 3rd party Information Collumn B and return the specific date that matches?

    I've tried: =INDEX('3rd Party Information'!$A:$D,MATCH(1,('3rd Party Information'!A:A=$B12)*(('3rd Party Information'!B:B=$H$4)*('3rd Party Information'!B:B=$H$5)*('3rd Party Information'!B:B=$H$6)*('3rd Party Information'!B:B=$H$7)*('3rd Party Information'!B:B=$H$8)*('3rd Party Information'!B:B=$H$9)*('3rd Party Information'!B:B=$H$10)*('3rd Party Information'!B:B=$H$11)),0),7)

    And slight variations, to no avail.

    Please Help
    Attached Files Attached Files
    Last edited by Andre24; 04-18-2019 at 09:21 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Vehicle Maintenance Forecast help please!

    I think you may have uploaded the wrong file. I don't see a '3rd Party Information' tab, and the file doesn't appear to be associated with vehicle maintenance.

  3. #3
    Registered User
    Join Date
    03-30-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    9

    Re: Vehicle Maintenance Forecast help please!

    Apologies,

    Work book was from a previous post requesting help.

    Uploaded correct workbook now.

    Apologies again

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Vehicle Maintenance Forecast help please!

    You have multiple events on the same date for each vehicle, and multiple events for the same time span (monthly, yearly, etc.) for each vehicle with different dates. It's a bit unclear as to what you hope to see on the Overview tab. Can you provide some examples of expected dates for Vehicle 1?

  5. #5
    Registered User
    Join Date
    03-30-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    9

    Re: Vehicle Maintenance Forecast help please!

    Hi Melvosh, thank you for the reply. I'll try to explain.

    I tried to catagorise the different maintenace events together. 1) To reduce the number of cullumns required on Overview Tab and 2) Not to have too many empty cells across all those collumns should i have 1 for each event.

    Each Vehicle has a unique fleet identifying number, so if that is criteria 1, Vehicle 1 = Vehicle 1, then I need it to match which maintenace event out of (for example) H4:H11 matches the Maintenace Event Name in 3rd Party Information Tab B:B and return that date.

    It should look like: Vehicle 1 = Vehicle 1
    H5 = '3rdPartyInformation '!C6
    Returns - 11 Apr 19

    If i specify the relevant cell like: {=INDEX('3rd Party Information'!$C:$C,MATCH(1,('3rd Party Information'!A:A=$B12)*('3rd Party Information'!B:B=5H),0))} , as an array formula, then the correct date is returned. But that does not help me to copy the formula accross or down.]

    Thank you again for casting your eyes over it!
    Last edited by Andre24; 04-18-2019 at 02:28 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Vehicle Maintenance Forecast help please!

    Still not clear, sorry. Explain how the maintenance events across the top of the overview tab link to those listed for each vehicle on the 3rd party tab.

    Please upload the workbook again - this time, please MANUALLY fill in all the details you expect to see filled in on the overview tab for vehicle 1.

    I suspect this is easy, but I need to understand your logic before suggesting a solution.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Vehicle Maintenance Forecast help please!

    I agree with Ali, I'd like more clarification. Examples of what you're expecting would be very helpful. Some of the questions that come to mind:
    • What happens if you have more than one event in the same category (e.g. Weekly / Monthly) with different dates?
    • You have events on the Overview sheet that don't show up in 3rd Party Information, and vice versa. Are those ignored?
    • Are you looking for exact text match between the two sheets for maintenance events?
    • What are all the OOPS events listed? Are those ignored?
    I think we can help, but we need to understand exactly what you're looking for. As Ali often says, the expected results are probably very obvious for you, but those expected results won't be as obvious for us.

  8. #8
    Registered User
    Join Date
    03-30-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    9

    Re: Vehicle Maintenance Forecast help please!

    Hi guys and really do apreciate your willingness to help.

    I've uploaded the workbook again with the first 10 vehicles completed. (manually selected the cells that match the relevant event name). I can carry on and manually do it for the rest of the vehicles, but my curiosity is peaked now and i want to see if there is a solution that i can apply in other workbooks that i use at work.

    Might help if i explain that the dates i want in the overview tab, is the date that current maintence runs out and maintenace require redoing at each interval. Yes, we over maintain our vehicles :P

    To answer Melvosh's questions, 1) If there are one or more events in the same catagory - It will on vehicles 25 and 26. To that end, I'll remove the weekly event and only have the monthly as an option.
    2)The events on the overview sheet are the main ones that's required, the rest will be ignored.
    3)Looking for exact matches yes please. I'll then figure out how to conditional format them in order to colour code each when they will require maintenance within 30 days/60 days or so on from =TODAY()
    4)OOPS, Out of Phase Maintenace occurse less regularily, i.e. 2,5 or 6 years. so nwill be ignored.

    I apologise for only giving a sanitised/small portion of the complete workbook, to comply with my organisation privacy rules.

    As always, really apreciate your help

  9. #9
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Vehicle Maintenance Forecast help please!

    I'm continuing to bat this around to see if I can come up with something, but I've also called for assistance Hopefully if I can't figure it out, someone else will be along to help.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  10. #10
    Registered User
    Join Date
    03-30-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    9

    Re: Vehicle Maintenance Forecast help please!

    I've decided to change it all a bit...

    I've made a calendar instead and want to forecast each event that happens in each month.

    I've been through various similar posts and been unable to use proposed solutions in my attempt.

    My fleet is managed by an 3rd party application. I run a report on there, export the results to excel, copy and paste values into this spreadsheet.

    My question is: Why does my formulas not identify the coppied data as dates?

    Please see attached spreadsheet to

    Please Help
    Last edited by Andre24; 05-24-2019 at 08:06 AM. Reason: Change Question

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need some help on my Vehicle Maintenance workbook
    By CLSSY56 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2018, 12:02 PM
  2. Vehicle Maintenance Spreadsheet
    By levpn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2018, 03:04 PM
  3. Index vehicle, driver and vehicle wise revenue report.
    By dackson in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 06-02-2017, 01:40 AM
  4. [SOLVED] Dynamic Vehicle Maintenance
    By LewisJ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2017, 05:56 AM
  5. Vehicle Maintenance Schedule
    By sbothe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2017, 11:33 AM
  6. Vehicle Maintenance Schedule
    By aspirus in forum Excel General
    Replies: 3
    Last Post: 05-19-2013, 09:33 PM
  7. vehicle fleet maintenance
    By diggy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1