Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Need A Excel Spreadsheet For Fleet Maintenance - With Formulas

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





Similar Excel Video Tutorials

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
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
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

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!


Hi All. This is my first post on any type of forum so please be gentle!!

I am hoping to get some help on what I am sure will prove to be a very simple problem so here goes.

Everyday i work with a small spreedsheat on which I have to enter the fleet number of all the vehicles that leave the depot, every fleet number has 5 digits and is unique, however the fleet numbers are not sequential and as the fleet changes so does the fleet list of numbers. I have already set up a data validation to highlight duplicate entries, but what I need is an error message or some other warning should I enter a 5 digit number that does not appear on our fleet list.

Daily entries appear on sheet 1 to 7, updated fleet list appears on sheet 8, the fleet list on sheet 8 is listed from A1:A200

At work we use XP and Excel 2003
At home Vista HP Excel 2007

I do hope this makes sense and any help would be very much appreicated


=IF(A4="",0,VLOOKUP(A4,'Fleet Maintenance Costs'!$A$3:$M$27,IF(E4

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.



Hello All,
I have to save data in .csv format to upload to my application.
my excel data is:
Month Issue Count
01/2008 Fleet 2
01/2008 HR 6
02/2008 Expense 1
02/2008 Fleet 2
03/2008 Fleet 3
03/2008 HR 2
03/2008 Other 1
when i save this to a .csv file and close and reopen, then the file looks like:
Month Issue Count
Jan-08 Fleet 2
Jan-08 HR 6
Feb-08 Expense 1
Feb-08 Fleet 2
Mar-08 Fleet 3
Mar-08 HR 2
Mar-08 Other 1
why is it happening this way.
i want the first column to be stored as mm/yyyy only.
If i click on the cell Jan-08 , the cell value it is showing is 01/01/2008.
request you to please help me regarding this.
Regards,
Dhanya


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 a maintenance schedule for a fleet of 100 trucks. Not all come due at
the same time. I have a spreadsheet set up now that calculates when the PM's
and Annual Inspections are due but I would like something that stands out
when I look at the list to say yes, this unit is due this month, without
having to go through the whole list and manually pick out the ones due for
Jan. Then next month, have all due in Feb. be highlighted or maybe 5 days
before the new month begins.



I want to consolidate information in a column that has a drop down list so as to total the costs adjacent to this information to form a total cost per item (items may appear more than once). See below:

Type Total
Council Rates $522.20
Insurance $777.64
Water Charges $156.03
Repairs and Maintenance $60.00
Stationery, telephone & postage
Repairs and Maintenance $85.29
Repairs and Maintenance $484.00
Repairs and Maintenance $155.10
Repairs and Maintenance $75.00
Water Charges $279.06
Council Rates $507.20
Repairs and Maintenance $60.00

I want to have one total per type of expense. I've tried many different formulas but they have not worked fully. Can anyone assist?

From a very frustrated user with little Medium to Advanced Excel knowledge.


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


First off "Hi", this is my first post to the forum!
I need some help, I have been cutting my teeth on VBA but this has me stumped.
First I will describe the sheet and then what I am after happening.
It is used as a pictoral planning calendar for a small fleet of vehicle's that are maintained on a running hours basis. Column D shows the Variant of vehicle , lets say F,H,B,Z &Y. To the right of that there are many columns (out to March 2011 at present) each one representing a week and counting down the hours based upon typical consumption to the next maintenance. The maintenance events are identified by letters. With me so far?
Right, what I am after is, upon opening the sheet a message box comes up and tells me how many Mk Y's I am going to have for the period between columns AS to BE. Anyone got any suggestions?

regds

CTSNWI


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


Dear All,

Please find the attached file.
Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.

thanks in advance

Regards,
mikello


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 currently have a recorded macro that creates two pivot tables in excel. The row is three levels deep. State, City, Name. I am trying to find some code to insert that will collapse the fields down to the State level in both tables.

Fleet Monthly

A B C 6 FLEET 12 815 7 AZ 57 8 PHOENIX 57 9 HERTZ-PHOENIX 57 10 CA 6 143 11 LOS ANGELES 5 54 12 HERTZ 5 54 13 SACRAMENTO 1 14 HERTZ RENT A CAR 1

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

What I want is this
Fleet Monthly

A B C 5 Row Labels 6 FLEET 12 815 7 AZ 57 8 CA 6 143 9 CO 92 10 IL 1 345

Thanks in advance for the help.




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))


hi, i'm working on a worksheet that highlights when maintenance needs to be carried out on various buildings.
i'd like to highlight the date due cells in different colours.

green for "in date"
orange when there is a month to go until maintenance is due
red when the maintenance is due or overdue

the present date is displayed at the top of the sheet with various dates of maintenance due in the cells beneath.

i have tried using excel help but cannot fathom this one.

hope someone can help...its been so long since i've used excel and i am definately rusty!

cheers

Al


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




Ok i have scoured this forum and the net and although i have seen several links showing how to do a VB script to count the cells in an excel forum many of them do not help me with my problem.

The issue im having is that I need to count up schedule blocks. If you notice in the attached spreadsheet i may have a full block of time or one with breaks in it. I also might have some blocks which have maintenance. Both the blocks which have maintenance and are blank should not be counted towards the total hours although maintenance should be counted towards the maintenance hours.

Excel example: https://dl.dropboxusercontent.com/u/...0schedule.xlsx

I used the code on this link: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

But the problem is that this code only sees the limited colors of excel and cannot tell the difference between maintenance and level 5 work colors.

So i used the code from Marcol at this link:http://www.excelforum.com/excel-prog...e-of-cell.html

The problem is i am having a difficulty merging both the code and getting it to work and i was hoping that one of you might be able to help me.

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.