I created a workbook with two sheets - the first is Service History
garage.xls |
---|
|
---|
| A | B | C | D | E |
---|
1 | Vehicle | inspection 1 | inspection 2 | inspection 3 | inspection 4 |
---|
2 | | | | | |
---|
3 | ZX342 | 24 Dec 04 | 11 Feb 05 | | |
---|
4 | sf2536 | 03 Jan 05 | 13 Feb 05 | 27 Mar 05 | |
---|
5 | gd7635 | 08 Dec 04 | 12 Feb 05 | 08 Apr 05 | |
---|
6 | er2635 | 01 Apr 05 | | | |
---|
7 | erd342 | 02 Oct 04 | 01 Jan 05 | 20 Feb 05 | |
---|
8 | wsq59 | 13 Dec 04 | 02 Feb 05 | 31 Mar 05 | |
---|
9 | asg653 | | | | |
---|
10 | ewp124 | 16 Feb 05 | 31 Mar 05 | | |
---|
11 | | | | | |
---|
|
---|
In A3
=Master!A3
and copied down column
This just picks up vehicle ids from Master sheet - I presume you might use registrations
Other entries should be self-explanatory - dates for each inspection for each vehicle. Dates can be in any format you want as long as excel recognises them as dates
Next worksheet is Master
Vehicle IDs and inspection periods in columns A and B
In C3 and copied down column
=IF('Service History'!B3="","none",LOOKUP(9.99999999999999E+307,'Service History'!3:3))
which shows the last inspection for each vehicle (or "none" if there are none)
as shown on "Service History" sheet
In D3 and copied down column
=IF(C3="none","",C3+LEFT(B3)*7)
which shows the date of the next inspection (or is blank if there have been no inspections for that vehicle) based on the number of weeks in column B.
You can conditionally format this column to highlight inspections due in the next week
Select column D
Use Format > Conditional Formatting
Use "Formula Is"
=AND(D1<>"",D1-TODAY()<7)
and select desired formatting
If you wished you could add additional conditional formatting to highlight, for instance, if the inspection date has passed(!) or if the inspection due date is less than 2 days away
Hope this helps