Forum Discussion
multi Vehicle maintenance log record
i need to make a vehicle maintenance log that will have 7 different vehicles, track all the oil changes, inspections, registrations that will notify us when something is expiring, and be able to attach receipts and documents to it.
what is the best way to do this?
In excel there are many ways to accomplish the same thing. That said I commend you on asking before you set up the workbook because there are definitely some things to consider that could be beneficial down the road. For example, many people would set up a new tab for each vehicle and then later ask about how to do xyz across all vehicles. Another common mistake is trying to make the tab look just the way you want the report to print and then ask how do I enter data so it goes here and moves the existing data down or blah blah blah..
SO, my recommendation is to create an all inclusive data entry tab. That tab would be a basic table with something like Date, VehicleID, milage, action, notes
of course you will pick the fields that best fit what you will need but VehicleID will need to be unique (does NOT need to be VIN number, just unique for your system)
Then every entry goes into that table. for example
2023-05-24 truck01 123,456 Oil Change Was qt low
You can then create a separate tab for reporting and have a pivot table or FILTER function and that report could be filtered based on Vehicle, Date range, type of service, or what ever you need
You can also create a report tab that shows any vehicle needing service (based on certain criteria)
I would also recommend creating another table for Vehicle Info. In that table you can have the VehicleID you want to use, make, model, year, VIN, etc (maybe Oil change milage, Oil change time, etc...)
And then you can also use Data Validation on the Input Table VehicleID column based on the Vehicle Info table to make sure you/they always use the right names for them. And then you can cross reference items like Oil change milage on the Vehicle Info table and last oil change from the Input Table to identify vehicles needing work.
Lastly I might recommend a table for allowable "Actions" to make/encourage that column to use the same terms like 'Oil Change', 'inspection', 'registration', 'other'
Finally, as for Attaching Receipts, excel isn't really set up to attach receipts, but you can create a folder and put those receipts in that folder and either add hyperlinks to them or even embed images in excel. If you enter the receipt filename in the input table (another column) then you can use the HYPERLINK(....) formula to dynamically create that hyperlink.
hope that helps
