How best to integrate/implement this?

[Ed from AZ]

I keep track of several vehicles and the lifespan of the replacement
parts for them. The operators turn in a useage log at the end of each
day with the ending miles and operating hours, and our maintenance
records log the miles and hours at which parts are replaced.

Unfortunately, it is all too common to have the hours meter go out.
And the replacement doesn't necessarily begin at zero! This made
calculating the total hours lifespan of any part a convoluted mess of
several calculations thorugh all the hours changes.

I built a spreadsheet that contains a table of the parameter changes;
now I can enter the actual vehicle parameters at time of maintenance
and when the part was originally installed and it returns the actual
hours lifespan. I have to use one worksheet per vehicle, because I'm
trying to get by without macros - code confuses the others who might
use this! (I'm wavering on this - it would be much easier to have a
single user form than jump from sheet to sheet!)

This is good for a simple report on one major item that needed
replacement. But here's my dilemma: I need to integrate/implement
this into a comprehensive Excel-based report. The comprehensive
report worksheet lists all major items for all the vehicles. When I
generate an update to the report, I enter the new end-of-day
parameters for each vehicle and formulas subtract the item
installation parameters from the ending parameters to return the
item's current lifespan. A macro then inserts these current values
into a Word report.

Due to the convolutions in the engine hours, though, some of the
returned values are three or four times too much, and some are even
negative! Somehow I need to use my calculator to at generate a "fudge
factor", or at best be inserted directly into the formulas to get a
correct return value.

How would those of you with experience integrate/implement somehting
like this?

Ed

 

[JLGWhiz]

Sounds more like a management question than a VBA question. However, when I
was in management, fudge factors had to be based on something that could be
measured, had been measured or should be measured. That means making up a
form and having some of the operators log in and out when their hour meters
fail. Take one or two vehicles whose drivers are willing to cooperate. Use
that data to figure your fudge factor and save the forms to back up your
actions.

 

[Ed from AZ]

Thanks for the response. I can't go into details, but it's definitely
not a management issue - it's just "the way things are". And I have
all the data for the changes to the parameters - that's what I'm using
for my "calculator".

My table looks like this:
Meter Meter Meter Meter
Instl-MI Instl-HR Rmvd-MI Rmvd-HR
5165.1 1036.8 5563.6 1062.9
5563.6 349.5 5696.8 357.2
5696.8 171.7 7042.5 267.6

Note that the Meter Rmvd miles on the first line matches the Meter
Instl miles of the second line. I calculate out the total hours from
each meter. And by making the Rmvd miles and hours in the last line
the current vehicle parameters, I can get the total hours to date
thought all the convolutions.

My "calculator" lets me enter the miles and hours at item failure and
the miles and hours at install, and returns the total hours lifespan.
For instance, for an item installed at 5200 miles and 1040 hours
(indicated), with the current parameters as shown in the last line of
the table, I get a lifespan of 126.5 hours.

As I said, I set this up to be on one worksheet per vehicle. and in a
separate workbook from my report master. The question is: how can I
use this when my VBA generates an updated report from the master to
return the corrected total lifespan for each item?

Perhaps link these worksheets into the report master and have the
update code pick up each and every parameter set and run it through
the correct calculator? Is there an easier way? Or just "brute
strength"?

Ed