Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Copying Specific Values From Sheet | Excel Discussion (Misc queries) | |||
Subset of one sheet on another sheet | Excel Discussion (Misc queries) | |||
Impoting data from Sheet 1 to Sheet 2 | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) |