Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Need A Excel Spreadsheet For Fleet Maintenance - With Formulas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More




View Answers     

Similar Excel Tutorials

Display all Formulas at Once in Excel
How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...
Quickly Convert Formulas into Their Output Values in Excel
This tutorial teaches you how to convert a formula or function into its displayed output in Excel. This is very im ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Using Parentheses in Excel
How to use parentheses in Excel in order to create more powerful formulas and functions. Sections: Basics of Parent ...

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Output the Name of the Current Excel Workbook Including Extension - UDF
- Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Similar Topics







Hi all,
I want to find out if it is possible to create custom formulas in the data
field of a pivot table.

I need to to show:
Fleets of vehicles.
How many of a particular fleet a particular company has on hand.
A percentage of how much of that companies fleet is not operational.
If no maintinane is completed between Now() and the end of the reporting
period, What the % of maintenance failure we will have for a fleet.

I think the best way to accomplish this is a pivot table but the built in
functions are not going to handle the calculations I need.

--
Thanks,

Patrick



I've tried two different formulas but neither of them work, this is what I'm trying to do:

I have an Equipment Input tab that our sales people select the equipment on a job, based on all the inputs, a depreciation value calculates. I'm trying to sum the depreciation for each individual piece of equipment, the same equipment type can be entered multiple times. Also, the equipment will not be entered in ascending order. Here are the two formulas I've tried

=IF('Generator & LT'!$B$4='Fleet Depr'!B4,'Generator & LT'!$AB$4,IF('Generator & LT'!$B$5='Fleet Depr'!B4,'Generator & LT'!$AB$5,IF('Generator & LT'!$B$6='Fleet Depr'!B4,'Generator & LT'!$AB$6,IF('Generator & LT'!$B$7='Fleet Depr'!B4,'Generator & LT'!$AB$7,IF('Generator & LT'!$B$8='Fleet Depr'!B4,'Generator & LT'!$AB$8,IF('Generator & LT'!$B$9='Fleet Depr'!B4,'Generator & LT'!$AB$9,IF('Generator & LT'!$B$10='Fleet Depr'!B4,'Generator & LT'!$AB$10,IF('Generator & LT'!$B$11='Fleet Depr'!B4,'Generator & LT'!$AB$11,0))))))))

But I max out on If Statements because I need about 10 more, and it doesn't sum the total if a piece of equipment is selected more than once.


The second formula I tried was

=IF(A6=0,0,LOOKUP(B6,'Generator & LT'!B4:B23,('Generator & LT'!AB4:AB23)))

I need it to sum the total if a piece of equipment is selected more than once.

Any suggestions??

Thanks


I created a long but simple formula and I have a problem that when I delete columns, the formula doesn't work any more (I get #REF). I wanted advise of first how to solve the deletion problem and if possible to shorten the formula (I thought if the formula will be smarter maybe it will solve the deletion problem).
The formula is:
=(SUM('Maintenance - Build'!D6:G6)*'Maintenance - Build'!$G$1)+(SUM('Maintenance - Build'!H6:K6)*'Maintenance - Build'!$K$1)+(SUM('Maintenance - Build'!L6:O6)*'Maintenance - Build'!$O$1)+(SUM('Maintenance - Build'!P6:S6)*'Maintenance - Build'!$S$1)+(SUM('Maintenance - Build'!T6:W6)*'Maintenance - Build'!$W$1)+(SUM('Maintenance - Build'!X6:AA6)*'Maintenance - Build'!$AA$1)+(SUM('Maintenance - Build'!AB6:AE6)*'Maintenance - Build'!$AE$1)+(SUM('Maintenance - Build'!AF6:AI6)*'Maintenance - Build'!$AI$1)+(SUM('Maintenance - Build'!AJ6:AM6)*'Maintenance - Build'!$AM$1)+(SUM('Maintenance - Build'!AN6:AQ6)*'Maintenance - Build'!$AQ$1)+(SUM('Maintenance - Build'!AR6:AU6)*'Maintenance - Build'!$AU$1)+(SUM('Maintenance - Build'!AV6:AY6)*'Maintenance - Build'!$AY$1)+(SUM('Maintenance - Build'!AZ6:BC6)*'Maintenance - Build'!$BC$1)+(SUM('Maintenance - Build'!BD6:BG6)*'Maintenance - Build'!$BG$1

It goes on and on for all the row cells.
Thanks!


What is a simply, accurate formula to calculate average fleet age when the fleet contains vehicles of different ages and different quantities of vehicles within each age group and fleet group.

See attached example.

Many thanks


I am looking for a Maintenance Follow-up schedule or Preventative Maintenance
Manager. It could show Daily, Weekly, Monthly, etc.



Sorry if this is a bit long-winded, just trying to paint the bigger picture.

Okay, I am running a spreadsheet collating the results of an online virtual
sailing competition. Usually, I create a table based on the order of the
entries, enter results each race, run a discard of worst result to get final
points and I rank across the whole fleet, use an
INDEX(range,MATCH(ref,range,0)) function to build a new table in rank order
and publish.

This series, we are following the new Olympic format, where the top ten
rankings after ten races and one discard are placed in the "gold" fleet, and
the remainder are in the "green" fleet. ONLY a boat in the Gold fleet can
win overall, after the following two races. The remaining boats will also
sail the same two races, but seperately.

To have the points work correctly, I need to rank races 11 and 12 within
each fleet, but without re-sorting the whole table, and hence I have
non-contiguous ranges for each fleet. Can I rank on non-contiguous ranges,
and can I make the ranges conditional on the fleet reference (this is a
column in the table using an if statement)?



Anyone know of a tool I could use, free or otherwise, for determining when a truck in a fleet of trucks has reached a point where it is smarter to buy a ne truck than continue to repair the old truck? I am trying to help with a fleet of 200+ trucks to develop a profile or general rule. I was hoping for an excel spreadsheet that generated several graphs and financial options.

I know there are many vairables to consider in the spreadsheet, some a
disposal cost or revenue from sale
age
mileage
replacement truck cost
replacement truck financing or lease info
depreciation plan
down time cost of repair days impact on utilization
etc.

Any wizards out there? I could use some help.

Thanks in advance

Mike


I have two worksheets Fixed cost and 2010 FC. I have values in column A and B of Fixed cost worksheet such AA0 in column A and 305 in column B. I am trying to find similar values in 2010 FC and if found I want their corresponding amounts in range C:L be entered in column H in Fixed Cost worbook same row as where the other values are entered.

Here is my base file where I need the information be entered.

Pivot

  A B C D E H I 6 Agency Fc Id Fc Description Appropriated Fund Sum of Centrally Managed (OPM) Forcast Variance 7 AA0 305 ELECTRIC  March estimate 100 930.98 0   8   404 Fleet other-please enter your Fleet other cost here. 100 15,992.89     9 AB0 404 Fleet other-please enter your Fleet other cost here. 100 9,052.05     10 AC0 309 RENT - Based on February estimate 100 374,118.51     11   310 Occupancy March estimate 100 2,216.01     12 AD0 309 Based on revised fixed costs from OPM in February 2008. 200 1,446,204.42    
Spreadsheet Formulas Cell Formula H7 =SUMPRODUCT((LEFT($AN$2:$BZ$2,LEN(CE3))=CE3)*($I$3:$I$318=$CD$2)*($AN$3:$BZ$318))

Excel tables to the web >> Excel Jeanie HTML 4

and here is the other file where I need to get the value
OPM

  A B C D E F G H I J K L 2     305 302 430 304 310 309 440 306 307   3 CODE Department Electric Fuel Janitorial GAS Occupancy Rent Total Security Steam Water/Sewer TOTAL 4 AA OFFICE OF THE MAYOR                          930.68                                  -                                  -                                      -                                     -                                         -                                      -                                 -                                   -                               930.68 5 AC AC-OFFICE OF THE AUDITOR                                    -                                    -                                  -                                      -                         2,216.02                       374,118.51                                    -                                 -                                   -                      376,334.54 6 AD AD - OFFICE OF THE INSPECTOR GENERAL                                    -                                    -                                  -                                      -                         8,619.50                  1,446,204.42                                    -                                 -                                   -                    1,454,823.92 7 AE CITY ADMINISTRATOR                     39,769.21                                  -                    21,051.92                                    -                     25,624.79                                       -                        15,147.30                               -                       2,169.76                     103,762.99 8 AF AF-CONTRACT APPEALS BOARD                                    -                                    -                                  -                                      -                         1,492.75                     251,047.58                                    -                                 -                                   -                      252,540.33 9 AM PROPERTY MANAGEMENT               1,856,775.96                423,687.56             745,496.62              2,205,329.37              2,139,073.28                                       -                    885,956.60              122,097.00               658,832.05                 9,037,248.43 10 AP ASIAN & PACIFIC ISLANDER AFFAIRS                                    -                                    -                                  -                                      -                                     -                                         -                                      -                                 -                                   -                                         -   11 AS FINANCE & RESOURCE MGT                     57,147.00                                  -                  30,250.88                                    -                     36,822.37                                       -                        21,766.31                               -                        3,118.40                      149,104.96
Spreadsheet Formulas Cell Formula L4 =SUM(C4:K4) L5 =SUM(C5:K5) L6 =SUM(C6:K6) L7 =SUM(C7:K7) L8 =SUM(C8:K8) L9 =SUM(C9:K9) L10 =SUM(C10:K10) L11 =SUM(C11:K11)

Excel tables to the web >> Excel Jeanie HTML 4

Thanks


OK here it is,

I don't know if i have come to the right place, I guess I will find out.

I am only new to Visual Basic... have experience with excel formula's etc. but have limited knowledge of VB. (But I am a fast learner)...

I have a task I am trying to do, which is a task I am hopeing to learn from.
What I am trying to do is measure 3 things in an excel spreadsheet (see attached). There are as follows:

* Average days for a quote to be completed
* Average days for each individual person to complete all there actions for each quote
* and the % of quote that have been completed on time if the allowable quote time is 10 days.

I want all statistics returned as per month of completion.

If date are balnk quote hasn't been completed
Qt numbers and lengths of quotes will continually change


QUOTE_NO REVISION LINE_NO CAP_MAINT DOM_EXP ITEM_TYPE QTY STEP ASSIGNEE DATE_*** DATE_REC DATE_COM DaysToComplete QT1 1 Maintenance Export Wheel 18 1 jblogs 01-Aug-08 04-Aug-08 06-Aug-08 5 QT1 1 Maintenance Export Wheel 18 2 adavids 06-Aug-08 08-Aug-08 14-Aug-08 8 QT1 1 Maintenance Export Wheel 18 3 acarlton 14-Aug-08 15-Aug-08 15-Aug-08 1 QT1 2 Maintenance Export Wheel 18 1 jblogs 01-Aug-08 04-Aug-08 06-Aug-08 5 QT1 2 Maintenance Export Wheel 18 2 adavids 06-Aug-08 08-Aug-08 14-Aug-08 8 QT1 2 Maintenance Export Wheel 18 3 acarlton 14-Aug-08 15-Aug-08 15-Aug-08 1 QT1 3 Maintenance Export Axle 18 1 jblogs 01-Aug-08 04-Aug-08 06-Aug-08 5 QT1 3 Maintenance Export Axle 18 2 adavids 06-Aug-08 08-Aug-08 14-Aug-08 8 QT1 3 Maintenance Export Axle 18 3 acarlton 14-Aug-08 15-Aug-08 15-Aug-08 1 QT2 1 Capital Domestic Set 52 1 acarlton 23-Jul-08 31-Jul-08 31-Jul-08 8 QT2 1 Capital Domestic Wheel 104 1 acarlton 23-Jul-08 31-Jul-08 31-Jul-08 8 QT2 1 Capital Domestic Axle 52 1 acarlton 23-Jul-08 31-Jul-08 31-Jul-08 8 QT2 1 Maintenance Domestic Axle 5 1 jblogs 09-Oct-08 21-Oct-08 28-Oct-08 19 QT2 1 Maintenance Domestic Axle 5 2 adavids 0 QT2 1 Maintenance Domestic Axle 5 3 carran 0 QT3 1 Maintenance Domestic Axle 30 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT3 1 Maintenance Domestic Axle 30 2 adavids 28-Jul-08 28-Jul-08 29-Jul-08 1 QT3 1 Maintenance Domestic Axle 30 3 acarlton 29-Jul-08 31-Jul-08 31-Jul-08 2 QT4 1 Maintenance Domestic Wheel 24 1 jblogs 23-Jun-08 23-Jun-08 15-Jul-08 22 QT4 1 Maintenance Domestic Wheel 24 2 adavids 15-Jul-08 15-Jul-08 15-Jul-08 0 QT4 1 Maintenance Domestic Wheel 24 3 acarlton 15-Jul-08 16-Jul-08 16-Jul-08 1 QT5 1 Capital Domestic Wheel 120 1 jblogs 23-Jun-08 23-Jun-08 27-Jun-08 4 QT5 1 Capital Domestic Wheel 120 2 adavids 27-Jun-08 29-Jun-08 11-Jul-08 14 QT5 1 Capital Domestic Wheel 120 3 acarlton 11-Jul-08 16-Jul-08 31-Jul-08 20 QT5 2 Capital Domestic Axle 60 1 jblogs 23-Jun-08 23-Jun-08 27-Jun-08 4 QT5 2 Capital Domestic Axle 60 2 adavids 27-Jun-08 29-Jun-08 11-Jul-08 14 QT5 2 Capital Domestic Axle 60 3 acarlton 11-Jul-08 16-Jul-08 31-Jul-08 20 QT7 1 Maintenance Domestic Wheel 24 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT7 1 Maintenance Domestic Wheel 24 2 adavids 28-Jul-08 28-Jul-08 29-Jul-08 1 QT7 1 Maintenance Domestic Wheel 24 3 acarlton 29-Jul-08 31-Jul-08 31-Jul-08 2 QT12 1 Maintenance Domestic Wheel 40 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT12 1 Maintenance Domestic Wheel 40 2 adavids 28-Jul-08 28-Jul-08 31-Jul-08 3 QT12 1 Maintenance Domestic Wheel 40 3 acarlton 31-Jul-08 06-Aug-08 15-Aug-08 15 QT9 1 Capital Domestic Set 1200 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT9 1 Capital Domestic Set 1200 2 adavids 28-Jul-08 28-Jul-08 31-Jul-08 3 QT9 1 Capital Domestic Set 1200 3 acarlton 31-Jul-08 31-Jul-08 31-Jul-08 0 QT9 1 Capital Domestic Wheel 2 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT9 1 Capital Domestic Wheel 2 2 adavids 28-Jul-08 28-Jul-08 31-Jul-08 3 QT9 1 Capital Domestic Wheel 2 3 acarlton 31-Jul-08 31-Jul-08 05-Aug-08 5 QT9 1 Capital Domestic Axle 1 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT9 1 Capital Domestic Axle 1 2 adavids 28-Jul-08 28-Jul-08 31-Jul-08 3 QT9 1 Capital Domestic Axle 1 3 acarlton 31-Jul-08 31-Jul-08 05-Aug-08 5 QT22 1 Capital Domestic Wheel 100 1 jblogs 23-Jun-08 23-Jun-08 28-Jul-08 35 QT22 1 Capital Domestic Wheel 100 2 adavids 28-Jul-08 28-Jul-08 01-Aug-08 4 QT22 1 Capital Domestic Wheel 100 3 acarlton 01-Aug-08 06-Aug-08 15-Aug-08 14 QT22 1 Maintenance Domestic Wheel 6 1 jblogs 23-Jun-08 23-Jun-08 04-Jul-08 11 QT22 1 Maintenance Domestic Wheel 6 2 adavids 04-Jul-08 04-Jul-08 14-Jul-08 10 QT22 1 Maintenance Domestic Wheel 6 3 acarlton 14-Jul-08 16-Jul-08 31-Jul-08 17 QT33 1 Maintenance Domestic Axle 10 1 jblogs 24-Jun-08 25-Jun-08 28-Jul-08 34 QT33 1 Maintenance Domestic Axle 10 2 adavids 28-Jul-08 28-Jul-08 01-Aug-08 4 QT33 1 Maintenance Domestic Axle 10 3 acarlton 01-Aug-08 04-Aug-08 05-Aug-08 4 QT33 1 Maintenance Domestic Wheel 100 1 jblogs 24-Jun-08 25-Jun-08 28-Jul-08 34 QT33 1 Maintenance Domestic Wheel 100 2 adavids 28-Jul-08 28-Jul-08 01-Aug-08 4 QT33 1 Maintenance Domestic Wheel 100 3 acarlton 01-Aug-08 06-Aug-08 08-Aug-08 7 QT33 1 Maintenance Domestic Axle 6 1 jblogs 24-Jun-08 25-Jun-08 28-Jul-08 34 QT33 1 Maintenance Domestic Axle 6 2 adavids 28-Jul-08 28-Jul-08 05-Aug-08 8 QT33 1 Maintenance Domestic Axle 6 3 acarlton 05-Aug-08 06-Aug-08 18-Aug-08 13 QT44 1 Maintenance Export Wheel 100 1 jblogs 25-Jun-08 30-Jun-08 28-Jul-08 33 QT44 1 Maintenance Export Wheel 100 2 adavids 28-Jul-08 28-Jul-08 05-Aug-08 8 QT44 1 Maintenance Export Wheel 100 3 acarlton 05-Aug-08 06-Aug-08 07-Aug-08 2 QT55 1 Maintenance Export Wheel 60 1 jblogs 07-Jul-08 07-Jul-08 20-Aug-08 44 QT55 1 Maintenance Export Wheel 60 2 adavids 20-Aug-08 04-Sep-08 29-Sep-08 40 QT55 1 Maintenance Export Wheel 60 3 carran 29-Sep-08 29-Sep-08 15-Oct-08 16 QT55 2 Maintenance Export Wheel 40 1 jblogs 07-Jul-08 14-Aug-08 20-Aug-08 44 QT55 2 Maintenance Export Wheel 40 2 adavids 20-Aug-08 21-Aug-08 29-Sep-08 40 QT55 2 Maintenance Export Wheel 40 3 carran 29-Sep-08 29-Sep-08 15-Oct-08 16 QT55 3 Maintenance Export Wheel 400 1 jblogs 07-Jul-08 07-Jul-08 20-Aug-08 44 QT55 3 Maintenance Export Wheel 400 2 adavids 20-Aug-08 21-Aug-08 29-Sep-08 40 QT55 3 Maintenance Export Wheel 400 3 carran 29-Sep-08 29-Sep-08 15-Oct-08 16 QT55 4 Maintenance Export Wheel 420 1 jblogs 07-Jul-08 07-Jul-08 14-Aug-08 38 QT55 4 Maintenance Export Wheel 420 2 adavids 0 QT55 4 Maintenance Export Wheel 420 3 carran 0


Hi, I am in process of creating a spreadsheet to track all maintenance activities on a piece of plant.

The tracker I have now has approx. 1000 entries for each maintenance job. I now recieive a weekly plan for future maintenance and am looking for some routine that I can compare the two spreadsheets for entries that I may already have made.

What I am looking for is a routine that will compare colums B & C in both spreadsheets for a mismatch as a match would mean I have already captured the details.

I am using Excel 2003


Any help would be much appreciated.

thanks

Salar




I'm responsible for setting up a compliance system for heavy vehicle maintenance.
I'd like to compile a spreadsheet that captures all the maintenance data, and prompts the relevant staff member when services and inspections are coming up.
There are other aspects, but that would be a great start.
Any ideas - existing spreadsheets?
Thanks,
Rob (aspirus)

Hello, this is my first post here. Hopefully I don't break any of the rules in posting...so here it goes:

I play an online browser game and I've made a spreadsheet for tracking various aspects of the game.

I do okay with the simple formulas and can haphazardly get excel to do what I want it to do. But for a few things I want it to do, I've run into a wall...

Here is the first one:

In the game, I receive reports like this:

Quote:

One of your fleets (Large Cargo:5 ) returns from [2:100:9] to Planet [2:101:13] . The fleet is delivering 58.679 Metal, 28.750 Crystal and 11.521 Deuterium.

I want to be able to copy that, and past it into A1. Easy enough, but here's the tricky part. I want excel to be able to automatically extract the numbers in front of metal, crystal, and deuterium into their own separate fieilds. So that in this instance B1 would display 58,670, C1 would display 28,750, and D1 would display 11,521.

The amounts can vary anywhere from 1 to 9,999,999 (but there will be no negative numbers), and in some cases the number would end in 0 (I've seen where that makes a difference in some formulas for some reason).

If it makes it easier, the copy/paste could be reduced to:

Quote:

The fleet is delivering 58.679 Metal, 28.750 Crystal and 11.521 Deuterium.




I have created a workbook for keeping records of all maintenance to vehicles in our fleet.
I have a sheet called Vehicle maintenance input:
& then another 10 sheets with the vehicle callsign.
So
Sheet 1 :- Input all data sheet
Sheet 2 :- Y4
Sheet 3 :- Y7
& so on.
I need a formulae that will copy multiple info to the relevant sheets, I have found a way of copying multiple info, with the formulae below, but I can't get it to work if there is more than 1 sheet.

=IF(ISERROR(INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2)),"",INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2))


Hello Everyone,

I am trying to create a spreadsheet from a previous post that Smitty helped me with.

It will be a maintenance tracking sheet that I am using conditional formatting for the due dates in red. The spreadsheet will email the due date information and highlight the due date in red.

I used a formula for the dates since some items are due weekly and others are monthly, 2 weeks, etc..

Column D has the start dates of the items. I then placed in formulas like: =DATE(YEAR(D2),MONTH(D2)+1,DAY(D2)) based on the time frame to check the maintenance item. I then dragged this date to the right creating the new future dates for maintenance schedule.

The problem is the code is not registering the information in the range that I have. It is only going over to column D but not filtering through the columns with the formulas.

I searched but think I am not looking in the right places to relate the existing formulas in the spreadsheet with the code.

Please help me with the code to review the entire range of dates d2:t23 not just the column D.

Thanks in advance for any help!!!



Code:

Option Explicit
Sub Check_Date_Send_Mail()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnDate As Range, rnValue As Range
Dim stAddress As String, stMsg As String
Dim stRecipient As String, stSubject As String
Dim stPost As String
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set rnDate = .Range("D2:T23")
End With
stMsg = "THE FOLLOWING MAINTENANCE ITEMS NEED TO BE CONDUCTED:"
        
With rnDate
Set rnValue = .Find(What:=Date)
If Not rnValue Is Nothing Then
stAddress = rnValue.Address
Do
With rnValue
stMsg = stMsg & "%0A" & Cells(.Row, "A") & ", " & Cells(.Row, "B") & ", " & Cells(.Column) & "," & rnValue
End With
Set rnValue = .FindNext(rnValue)
Loop While Not rnValue Is Nothing And rnValue.Address  stAddress
Else
MsgBox "NO MAINTENANCE DUE TODAY.", vbInformation
Exit Sub
End If
End With
stRecipient = "email@domain.com"
stSubject = "Plant Maintenance"
stPost = "mailto:" & stRecipient & "?"
stPost = stPost & "subject=" & stSubject & "&"
stPost = stPost & "body=" & stMsg
ActiveWorkbook.FollowHyperlink (stPost)
End Sub

Private Sub Workbook_Open()
End Sub


Note: trying to figure out how to add the spreadsheet, I do not see paperclip or way to insert the spreadsheet


Hello,

I am trying to do a sheet which plots the month of each maintenance depending on its next date and the frequency. I cant seem to get my head around it!!

Can anybody help? basically I need a 1 in each of the month the maintenance will hit yearly.
so if the job was for march and the freq is 4 then i need a 1 in mar, jun, sep, dec and the rest will be 0.

Any help will be appreciated, thanks in advance!

Kong.


You guys have all been a tremendous help for me in the past. I have learned a lot about the more complex excel formulas just for some help. But now I am trying to make a very large spreadsheet for the company I work for that will help us keep track of our fleet. There are some formulas that would help us do this, but I dont know them.


Problem 1
The first thing I need help with is a table I made of all of our drivers. The drivers name is on the left side of the table, and there is other information along the top of the chart. The first column (B) of the table should count the number of driving complaints that each driver has. So the formula should look for the drivers name in column A, find the worksheet with that same name, and count the number of occurances for that name in row F on a tab called "Driving Violations". If count cannot search an entire column, then it can just look at F2:F200 or so.



Problem 2
The second column on the table (C) is in referrence to a certifiction that all of our technicians have. This column should look at the techs name in row A, move to the worksheet "employees", find that name in row a, then find the certifications expiration date from column D, and return that date less three months. The three months is just to give me time to get the tech in a class before the certification expires.

Back on my "Fleet" tab, I have column D as an if statement. Yes is returned if the odometer is greater than the oil change mileage. The only thing is that I want some advanced notice so I can tell my boss what to expect.

heres the formula i am using now:
IF(MAX(INDIRECT(A3&"!"&"d2:d500"))>MAX(INDIRECT(A3&"!"&"e2:e500")), "Yes", "No")

I want yes to return if max of row E minus max of row D is less than or equal to 500 miles.


This is still a work in progress, so I might ask more questions later. But Ill try to figure the rest out on my own first.
Thanks for you help!


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, have a spreasdsheet that opens a spreadsheet and then executes a spreadsheet in the newly opened workbook. My code is below. The code keeps debugging on executing the named macro..

Application.ScreenUpdating = False
ChDir "M:\Valuation\Valuation Comparisons"
Workbooks.Open Filename:= _
"M:\Valuation\Valuation Comparisons\AUD Fixed Rate Maintenance.xlsm"

Windows("AUD Fixed Rate Maintenance.xlsm").Activate
Application.Run "AUD Fixed Rate Maintenance.xlsm!Import_Ratesheets"


This debug error is saying “The macro may not be available In the workbook or all macros maybe disabled”

‘’ Macro in the sheet AUD Fixed Rate Maintenance.xlsm”

Sub Import_Ratesheets()

'Macro to upload rate sheets from the various brokers


Dim result As Integer

Help!

Ray


Hi,

Does anyone have vba code to trim and insert text? What i'm looking for is to trim all text in column G left and then insert 23 spaces before the text in black, keeping the text in red left. I have several thousand rows of data similar to that shown and so a macro would save time.

Any help greatly appreciated
******** ******************** ************************************************************************> Microsoft Excel - CONCEPT.XLS ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A2 =
A B C D E F G 2 1 0 P B 1 SYSTEM: 3 1 0 P B 2 4 1 0 P B 3 ORGANIZATIONAL LEVEL - SCHEDULED MAINTENANCE: 5 1 0 P B 4 NONE. 6 1 0 P B 5 7 1 0 P B 6 UNSCHEDULED MAINTENANCE: 8 1 0 P B 7 NONE. 9 1 0 P B 8 10 1 0 P B 9 11 1 0 P B 10 INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE: 12 1 0 P B 11 NONE. 13 1 0 P B 12 14 1 0 P B 13 UNSCHEDULED MAINTENANCE 15 1 0 P B 14 NONE. 16 1 0 P B 15 17 1 0 P B 16 18 1 0 P B 17 DEPOT LEVEL - SCHEDULED MAINTENANCE 19 1 0 P B 18 NONE. 20 1 0 P B 19 21 1 0 P B 20 UNSCHEDULED MAINTENANCE: 22 1 0 P B 21 NONE. CONCEPT
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




Hi,

I am setting up a maintenance schedule as part of our compliance with PUWER. I have set up an Excel sheet with the dates of the latest maintenance completed on each of our machines. I now need to 1) Add 6 months on to each date and 2) use the conditional formatting tool to ensure that each cell turns yellow 3 weeks before the planned date and then after this date goes back to being clear..

Cheers Chris



I have an excel sheet used by an operator to enter details of fuel delivery for the fleet on daily basis.

The fleet is of over 100 vehicles and equipment's.

The problem I currently face is anomalies in data entry. The operators are making TYPO's while entry mileage/ODO meter of equipment.

Is there someway in which i can use excel to detect errors,

For example, If an operator enters a mileage of 145205 on Monday, the equipment refuels on Thursday, with mileage, 145225, but the operator enters 1452250.

I am also attaching the Sample sheet with a month data for Three equipment's.

The Column in shaded in yellow is the mileage column. Each equipment has a unique fleet number.

How to use Excel to determine maintenance Costs...Been playing with VLOOKUP but it doesn't seem to be working...Should I be using something else?


I work for a small company with 2 aircraft (soon to be 3). I would like to be able to input all my maintenance Invoices & the hours per month that that aircraft flies to determine what my hourly cost is.

I have 3 sheets.

The 1st Sheet is where I want to be able to input what aircraft & during what dates I want to look at @ have it give me the computed information.

The 2nd Sheet is where I list the maintenance invoices. Here each aircraft is listed when it was repaired, how much, ect.

The 3rd Sheet Has the Aircraft monthly hours. The Idea is to have these hours devided into the sum of the maintenance performed on that aircraft durring the aloted time period.

For example.

Durring 2008 the Aircraft flew 678 hours & The Maintenance spent on it was $43210. Thus 43219 divided by 678 = $63.73 per hour.


Any ideas?....So far all my attempts just end up with error messages.
Any suggestions would be greatly appreciated. I've included a copy of my current attempt.
http://simplycakes.info/PAA%20-%20Ma...ce%20Costs.xls


Morning all

I am looking to put togther a small fleet truck mileage tracking spreadsheet, new, but manually backfitted with data from an old, really ugly, spreadsheet. The current person doing the entry is not detail oriented so I would like to lock down most information except for the data entry. The ones that maintained the old sheet would just copy and paste the info and bring all the baggage and formatting and extracting the information is like pulling teeth. There a couple dozen sheets (not in the same format) roughly based on date.

The current daily entries would be truck, driver, odometer start, odometer end, date, date, and hours but most of the older the data (a year to two years old) contains just dates and miles periodically. There are about 40+ trucks in the fleet and they come and go depending on age, mileage, and condition.

What would be the best way to arrange the data to be able to provide the most useable information to satisfy a PR hungry manager? ...minimize or ease data entry?

They seem to like things like -- "the fleet has reached the 7 million miles" or this truck has put in 100,000 miles this year, or this one has 160,000 miles and is ready for a rebuild, or this truck averages 200 miles/day. Each truck runs an average of 60,000 miles per year depending on work load. I'm not used to working with running or accumulating data and dates and I am not sure of the best way to present the information (i.e. pivottable, dashboard, or basic report).


Good Morning

I'm working on a sreadsheet for inserting and collating information for a fleet of vehicles.

I have userforms for inserting various pieces of information when the vehicle is new or leaving the fleet.

I would like to use a userform for inserting weekly information. I know how to insert the basic information in columns but I'm stumped for working in rows and with inserting information at different times.

Could someone please help me with the code, I have attached a sheet showing the kind of layout I am thinking off. The vehicle registration will be picked up from another spreadsheet

Thanks for your help


Hi All,
In my workbook I have a fleet maintenance calendar and it has start/stop dates and a charector in columns B-D for planned events. I need a script to look at the dates and then populate the corresponding cells in that row with the charector. The dates are in row 1 and it is on day per column format, if no event is planned the cells in B-D are blank.
Any ideas?