Service record sheet

We have a fleet of vehicles that are serviced at 4week, 5week and 6week
intervals. I would like a workbook with 3 sheets covering the different
service intervals and 52 sheets, week1,2,3....52.
The 4,5 and 6 week service sheets would have the vehicle reg and fleet No.
What I would like it to be able to do is for me to input a vehicle reg/fleet
No. into any of the weekly sheets and excell to check if it is 4,5 or 6 week
service and add it to the weekly sheets accordingly. This is way above my
head :-( any help if it can be done most welcome.
Thx in advance.
Kev

Hi kevhatch,

Your request isn't quite clear. Do you want to add those vehicles to weekly
sheets which are due to be serviced in that particular week? Otherwise put:
Do you want weekly sheets showing vehicles to be serviced in that week? If
so, do you have the date of the last service for each vehicle?
It doesn't seem necessary to input reg No.-s, but input week No. and ask
Excel (write a macro) to collect reg No.-s of vehicles to be serviced in that
week into the corresponding week sheet (that is those with which the service
interval elapsed since the last service date).

Regards,
Stefi



„kevhatch” ezt �*rta:

> We have a fleet of vehicles that are serviced at 4week, 5week and 6week
> intervals. I would like a workbook with 3 sheets covering the different
> service intervals and 52 sheets, week1,2,3....52.
> The 4,5 and 6 week service sheets would have the vehicle reg and fleet No.
> What I would like it to be able to do is for me to input a vehicle reg/fleet
> No. into any of the weekly sheets and excell to check if it is 4,5 or 6 week
> service and add it to the weekly sheets accordingly. This is way above my
> head :-( any help if it can be done most welcome.
> Thx in advance.
> Kev

All the vehicles we have are set in their respective sevice routines, what I
would like to be able to do is to open up a particular week to view which
vehicles are coming up for service. Also, as we acquire new vehicles, I could
enter them into the 4,5, or 6 week(as required by type of vehicle) sheet,
then enter reg/fleet No. into the first service week which would then
automatically follow on.

Hope this makes it a bit clearer.
Thx
Kev

"Stefi" wrote:

> Hi kevhatch,
>
> Your request isn't quite clear. Do you want to add those vehicles to weekly
> sheets which are due to be serviced in that particular week? Otherwise put:
> Do you want weekly sheets showing vehicles to be serviced in that week? If
> so, do you have the date of the last service for each vehicle?
> It doesn't seem necessary to input reg No.-s, but input week No. and ask
> Excel (write a macro) to collect reg No.-s of vehicles to be serviced in that
> week into the corresponding week sheet (that is those with which the service
> interval elapsed since the last service date).
>
> Regards,
> Stefi
>
>
>
> „kevhatch” ezt �*rta:
>
> > We have a fleet of vehicles that are serviced at 4week, 5week and 6week
> > intervals. I would like a workbook with 3 sheets covering the different
> > service intervals and 52 sheets, week1,2,3....52.
> > The 4,5 and 6 week service sheets would have the vehicle reg and fleet No.
> > What I would like it to be able to do is for me to input a vehicle reg/fleet
> > No. into any of the weekly sheets and excell to check if it is 4,5 or 6 week
> > service and add it to the weekly sheets accordingly. This is way above my
> > head :-( any help if it can be done most welcome.
> > Thx in advance.
> > Kev

Here is another option to consider:

If I understand correctly, your 52 weekly worksheets simply list the vehicle
name and a serial number. Why not put all that data into a single worksheet
with 54 columns: one column for the name, one for the serial number and one
for each week. Then you could put an "x" into the cells to indicate which
weeks each vehicle needs service. Autofilters would then allow you to look
at the list for any particular week.


"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> All the vehicles we have are set in their respective sevice routines, what
> I
> would like to be able to do is to open up a particular week to view which
> vehicles are coming up for service. Also, as we acquire new vehicles, I
> could
> enter them into the 4,5, or 6 week(as required by type of vehicle) sheet,
> then enter reg/fleet No. into the first service week which would then
> automatically follow on.
>
> Hope this makes it a bit clearer.
> Thx
> Kev
>
> "Stefi" wrote:
>
>> Hi kevhatch,
>>
>> Your request isn't quite clear. Do you want to add those vehicles to
>> weekly
>> sheets which are due to be serviced in that particular week? Otherwise
>> put:
>> Do you want weekly sheets showing vehicles to be serviced in that week?
>> If
>> so, do you have the date of the last service for each vehicle?
>> It doesn't seem necessary to input reg No.-s, but input week No. and ask
>> Excel (write a macro) to collect reg No.-s of vehicles to be serviced in
>> that
>> week into the corresponding week sheet (that is those with which the
>> service
>> interval elapsed since the last service date).
>>
>> Regards,
>> Stefi
>>
>>
>>
>> "kevhatch" ezt �rta:
>>
>> > We have a fleet of vehicles that are serviced at 4week, 5week and 6week
>> > intervals. I would like a workbook with 3 sheets covering the different
>> > service intervals and 52 sheets, week1,2,3....52.
>> > The 4,5 and 6 week service sheets would have the vehicle reg and fleet
>> > No.
>> > What I would like it to be able to do is for me to input a vehicle
>> > reg/fleet
>> > No. into any of the weekly sheets and excell to check if it is 4,5 or 6
>> > week
>> > service and add it to the weekly sheets accordingly. This is way above
>> > my
>> > head :-( any help if it can be done most welcome.
>> > Thx in advance.
>> > Kev

Realizing Lewis's idea:

Sheet1 columns:
A B C D
E F BD
Date of
w e e k s
Reg. Fleet No. Last service Interval W01 W02 ... W52
xxx xxx mm/dd/yyyy 4or5or6

Sheet2 columns
A B
First Monday 03/01/2005

The subs:


Sub allrows()
vehnum = Cells.Find("*", Range("A1"), xlValues, xlPart, xlByRows,
xlPrevious, False).Row
For v = 2 To vehnum
Call servmark(v)
Next v
End Sub
Sub singlerow()
v = ActiveCell.Row
Call servmark(v)
End Sub
Sub servmark(vehrow)
Dim vehicle, week As Range
firstmonday = Sheets("Sheet2").Range("B1")
servintval = Cells(vehrow, 4) '4,5,6
Set vehicle = Range(Cells(vehrow, 5), Cells(vehrow, 56)) '"E2:BD2"
vehicle.ClearContents
weeknum = 0
For Each week In vehicle
weeknum = weeknum + 1
lastservice = Cells(vehrow, 3) '"C2"
weekstart = firstmonday + (weeknum - 1) * 7
weekend = firstmonday + weeknum * 7
servivs = 52 \ servintval + IIf(52 Mod servintval > 0, 1, 0) '13,
10,4 8,67
For i = 1 To servivs
dueservice = lastservice + i * servintval * 7
If dueservice >= weekstart And dueservice < weekend Then
week.Value = "X"
Exit For
Else
week.Value = ""
End If
Next i
Next week
End Sub

Then Autofiltering week columns you will see the vehicles to be serviced in
that week. Don't forget to rerun macros after inserting new vehicle or
changing date of last service!

Regards,
Stefi

„Lewis Clark” ezt �*rta:

> Here is another option to consider:
>
> If I understand correctly, your 52 weekly worksheets simply list the vehicle
> name and a serial number. Why not put all that data into a single worksheet
> with 54 columns: one column for the name, one for the serial number and one
> for each week. Then you could put an "x" into the cells to indicate which
> weeks each vehicle needs service. Autofilters would then allow you to look
> at the list for any particular week.
>
>
> "kevhatch" <[email protected]> wrote in message
> news:[email protected]...
> > All the vehicles we have are set in their respective sevice routines, what
> > I
> > would like to be able to do is to open up a particular week to view which
> > vehicles are coming up for service. Also, as we acquire new vehicles, I
> > could
> > enter them into the 4,5, or 6 week(as required by type of vehicle) sheet,
> > then enter reg/fleet No. into the first service week which would then
> > automatically follow on.
> >
> > Hope this makes it a bit clearer.
> > Thx
> > Kev
> >
> > "Stefi" wrote:
> >
> >> Hi kevhatch,
> >>
> >> Your request isn't quite clear. Do you want to add those vehicles to
> >> weekly
> >> sheets which are due to be serviced in that particular week? Otherwise
> >> put:
> >> Do you want weekly sheets showing vehicles to be serviced in that week?
> >> If
> >> so, do you have the date of the last service for each vehicle?
> >> It doesn't seem necessary to input reg No.-s, but input week No. and ask
> >> Excel (write a macro) to collect reg No.-s of vehicles to be serviced in
> >> that
> >> week into the corresponding week sheet (that is those with which the
> >> service
> >> interval elapsed since the last service date).
> >>
> >> Regards,
> >> Stefi
> >>
> >>
> >>
> >> "kevhatch" ezt �*rta:
> >>
> >> > We have a fleet of vehicles that are serviced at 4week, 5week and 6week
> >> > intervals. I would like a workbook with 3 sheets covering the different
> >> > service intervals and 52 sheets, week1,2,3....52.
> >> > The 4,5 and 6 week service sheets would have the vehicle reg and fleet
> >> > No.
> >> > What I would like it to be able to do is for me to input a vehicle
> >> > reg/fleet
> >> > No. into any of the weekly sheets and excell to check if it is 4,5 or 6
> >> > week
> >> > service and add it to the weekly sheets accordingly. This is way above
> >> > my
> >> > head :-( any help if it can be done most welcome.
> >> > Thx in advance.
> >> > Kev
>
>
>