Vehicle and Trailer inspection sheets

David Jones

New Member
Joined
Mar 3, 2019
Messages
6
Hi All,

I need to carry out 6 weekly and 10 weekly inspections on our units and trailers. How do I set up the sheets so that:-
1) It automatically records the next date of Inspection and respective dates for the next 6 months so that I have a list of due and completed inspections.
2) On the 2nd page/sheet, I want to have the the identical list of trailers, but instead of showing the inspections as being due in either 6 weeks or 10 weeks, I want to be able to see what vehicle and/or trailer is currently (within the next 7 days) due inspection


Thank you

David
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
You need two databases. One to record the history of inspections per trailer and one to keep the status of the trailer. The status DB is going to show the last inspection and the next inspection date. You can create Conditional Formatting on the Status DB to show upcoming inspections with a certain date range. You can filter and sort the Status DB so it shows a chronological upcoming inspections list.

Take it one step at a time and ask for help.

Welcome to the site.

Jeff
 

David Jones

New Member
Joined
Mar 3, 2019
Messages
6
Hi Jeff,

Thank you for your reply. I can use Excel, but I am very much a novice when it comes to setting up databases,etc. I most certainly will need a lot of help.

Thank you

David


You need two databases. One to record the history of inspections per trailer and one to keep the status of the trailer. The status DB is going to show the last inspection and the next inspection date. You can create Conditional Formatting on the Status DB to show upcoming inspections with a certain date range. You can filter and sort the Status DB so it shows a chronological upcoming inspections list.

Take it one step at a time and ask for help.

Welcome to the site.

Jeff
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
A database (DB) is nothing more than a table on a sheet. Each row is a record. Columns of data are called fields. Usually, the left most column is used for a unique field that identifies each trailer or piece of equipment; no duplicates. It could be a serial number or VIN. A Short name field is helpful and so is a description field. What fields you put after that is up to you. You can add more later.

In your case, you wanted to track when a trailer needs inspection. If you have different types of inspections, then you will need different fields to indicate completion of the last inspection and next inspection for each type. You could include a field to indicate the period of time between each inspection. You might want to include the result of the last inspection. You might want to include a field to indicate special requirements for the next inspection. This "Inspection Status" DB will change all the time. Meaning, you won't have multiple lines for each trailer as record, each record will change as an inspection is completed.

The other DB you need, I think, is an "Inspection History" DB. Setup the same way as the other, but the Unique identifier field will have duplicates. Because you're just maintaining a historical inspection db. You won't need the fields that indicate the next inspection date.

Next, you can learn how to filter the data and create conditional formatting on the inspection dates to indicate upcoming inspections. I can show you how to create formulas to count the number of upcoming inspections. We can create Date Range Cells or length of time to control the variable for the conditional formatting. I can show you how to create VBA code that will automatically filter the inspections based on the Date Range.

Get your databases created.

Jeff
 

David Jones

New Member
Joined
Mar 3, 2019
Messages
6

ADVERTISEMENT

Thanks Jeff
I appreciate your help.
I have created my data base and presumably, will mirror database1 in sheet 2. With the exception of future dates, only actually completed inspection dates. Will this automatically copy from sheet1 to sheet2 on completion of the inspection.
I would also like every second inspection to include a brakes test, is this possible to show?
I am at 1st base ( with the data base completed), can you please get me to 2nd base?

Many thanks

David
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
I'm still with you. OK, it would be nice to have a list of your fields. Can you type out your column headers for each DB please.

Concerning your brakes test inspections. As I said before, you can have multiple inspection types in your Inspection Status DB. For instance:

Excel 2013/2016
ABCDEFGHI
1Item #Last Tire DateNext Tire Insp DateLast Tire ResultNext Tire NeedsLast Break DateNext Break DateLast Break ResultNext Break Needs
2TR147781/1/20196/1/2019@ 30% treadLook for side wall wear, Uneven wear on right inside1/1/20198/1/2019Pads on both sides at 50%Check drum parts

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
When all data is in, you can filter the data by inspection dates; you could select to filter by the next tire inspection or by the next break inspection. I would add a column that calculates the earliest date for all inspections so you can filter all the inspections by date range.

To answer your question about getting data from one DB to another. I would create some macros to help with this or make some formulas on the Inspection Status DB. Your Inspection History DB should be the master data set, IMO. You could go a couple ways. We can create formulas to find the last date of inspection for each inspection type. You could have a field on the Inspection Status DB that allows you to set the period of time between inspections, then simply calculate the next inspection date. Do you usually work in Months, Weeks, or Days?

Jeff
 

David Jones

New Member
Joined
Mar 3, 2019
Messages
6

ADVERTISEMENT

I'm still with you. OK, it would be nice to have a list of your fields. Can you type out your column headers for each DB please.

Concerning your brakes test inspections. As I said before, you can have multiple inspection types in your Inspection Status DB. For instance:

Excel 2013/2016
ABCDEFGHI
1Item #Last Tire DateNext Tire Insp DateLast Tire ResultNext Tire NeedsLast Break DateNext Break DateLast Break ResultNext Break Needs
2TR147781/1/20196/1/2019@ 30% treadLook for side wall wear, Uneven wear on right inside1/1/20198/1/2019Pads on both sides at 50%Check drum parts

<tbody>
</tbody>
Sheet1
When all data is in, you can filter the data by inspection dates; you could select to filter by the next tire inspection or by the next break inspection. I would add a column that calculates the earliest date for all inspections so you can filter all the inspections by date range.

To answer your question about getting data from one DB to another. I would create some macros to help with this or make some formulas on the Inspection Status DB. Your Inspection History DB should be the master data set, IMO. You could go a couple ways. We can create formulas to find the last date of inspection for each inspection type. You could have a field on the Inspection Status DB that allows you to set the period of time between inspections, then simply calculate the next inspection date. Do you usually work in Months, Weeks, or Days?

Jeff


Hi Jeff,

Below is the type of layout that I refer to and this is only a part of it. Can you work with this?

Regards,

David
TrailerChassis NoMOT dateYearTail Lift Lola TestInspection DateInspection DateInspection DateInspection Date
MTT100SBC312228Feb-20201004/04/201916/05/201927/06/201908/08/2019
MTT02C241030Nov-19200715/03/201926/04/201907/06/201919/07/2019
MTT20C300808Jan-20201201/04/201910/06/201919/08/201928/10/2019
MTT34C346051Jul-19201204/03/201913/05/201922/07/201930/09/2019
MTT37C356564Nov-19201315/04/201924/06/201902/09/201911/11/2019
MTT38C356563Oct-19201301/04/201910/06/201919/08/201928/10/2019
MTT41C361685Feb-20201421/02/201902/05/201911/07/201919/09/2019
MTT42 (2020)C361720Mar-19201419/03/201928/05/201906/08/201915/10/2019
MTT43C361681Mar-19201410/04/201919/06/201928/08/201906/11/2019
MTT44C361682Mar-19201404/02/201915/04/201924/06/201902/09/2019
MTT45C361721Mar-20201428/02/201909/05/201918/07/201926/09/2019
MTT46C361684Apr-19201410/04/201919/06/201928/08/201906/11/2019
MTT47C361683Feb-20201412/02/201923/04/201902/07/201910/09/2019
MTT48C361680Mar-20201425/02/201906/05/201915/07/201923/09/2019
MTT49C361686Mar-19201406/03/201915/05/201924/07/201902/10/2019
MTT50C361687Mar-20201404/03/201913/05/201922/07/201930/09/2019
MTT51C253752Aug-19200825/03/201906/05/201917/06/201929/07/2019
MTT52C253755Jul-19200819/03/201930/04/201911/06/201923/07/2019
MTT53C253753Aug-19200818/03/201929/04/201910/06/201922/07/2019
MTT68C374154Sep-19201401/04/201910/06/201919/08/201928/10/2019
MTT69C374155Sep-19201422/04/201901/07/201909/09/201918/11/2019
MTT58DDC234717Nov-1921/01/201901/04/201910/06/201919/08/2019
MTT72DDC234723Sep-1926/03/201904/06/201913/08/201922/10/2019
MTT76DDC234733Nov-1915/03/201924/05/201902/08/201911/10/2019
MTT79DDC300575Oct-1925/04/201904/07/201912/09/201921/11/2019
MTT80DDC301387Nov-1908/01/201919/03/201928/05/201906/08/2019
MTT81DDC303015Jun-1909/01/201920/03/201929/05/201907/08/2019
MTT82DDC303027Aug-1911/03/201920/05/201929/07/201907/10/2019
MTT83DDC303038Mar-1916/03/201925/05/201903/08/201912/10/2019
MTT84DDC301089Mar-2003/03/201912/05/201921/07/201929/09/2019
MTT86DDC303028Jun-1907/03/201916/05/201925/07/201903/10/2019
MTT87DDC301249Nov-1911/03/201920/05/201929/07/201907/10/2019
MTT88DDC301400Jul-1927/02/201908/05/201917/07/201925/09/2019
MTT89DDC303017May-1901/04/201910/06/201919/08/201928/10/2019
MTT90DDC303021Jul-1911/04/201920/06/201929/08/201907/11/2019
MTT91DDC303055Jul-1927/03/201905/06/201914/08/201923/10/2019
MTT92SKC390485Feb-20201512/02/201923/04/201902/07/201910/09/2019
MTT93SKC390488Mar-20201506/03/201915/05/201924/07/201902/10/2019
MTT94SKC390487Mar-20201514/03/201923/05/201901/08/201910/10/2019
MTT95SKC390486Mar-19201515/01/201926/03/201904/06/201913/08/2019
MTT96SKC390489Feb-20201506/02/201917/04/201926/06/201904/09/2019
MTT97SKC390490Jan-20201529/01/201909/04/201918/06/201927/08/2019
MTT98SBC391894Jun-19201513/03/201922/05/201931/07/201909/10/2019
MTT99SCC385981Jun-19201506/03/201915/05/201924/07/201902/10/2019
S1C187979Oct-19200522/04/201903/06/201915/07/201926/08/2019
S2C187980Oct-19200529/04/201910/06/201922/07/201902/09/2019
S3C187984Jul-19200501/04/201913/05/201924/06/201905/08/2019
S5C187986Oct-19200514/03/201925/04/201906/06/201918/07/2019
S6C187987Nov-19200526/03/201907/05/201918/06/201930/07/2019
S7C187988Nov-19200529/03/201910/05/201921/06/201902/08/2019
S8C187990Dec-19200523/04/201904/06/201916/07/201927/08/2019
S10C187992Dec-19200509/02/201923/03/201904/05/201915/06/2019
S14C188606Oct-19200522/04/201903/06/201915/07/201926/08/2019
S15C188607Nov-19200526/03/201907/05/201918/06/201930/07/2019
SB2001C292703Aug-19200914/01/201925/02/201908/04/201920/05/2019
SB2002C292704Aug-19200922/02/201905/04/201917/05/201928/06/2019
SB2003C292708Sep-19200902/04/201914/05/201925/06/201906/08/2019

<colgroup><col span="4"><col><col><col><col><col></colgroup><tbody>
</tbody>
 

David Jones

New Member
Joined
Mar 3, 2019
Messages
6
Hi Jeff,

I forgot to mention that inspection dates vary according to the age of the trailer. So dates range from 6 weekly to 10 weekly inspections. Some are even at 12 weeks (Hire trailers).

Kind regards,

David
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
I tried to paste the data into Excel. Not a good format. Have you looked at the forum tools you can download? I use the Mr Excel HTML Maker to copy worksheet data. Very easy to use.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,429
Messages
5,528,705
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top