+ Reply to Thread
Results 1 to 5 of 5

Alerts?

  1. #1
    audiophile
    Guest

    Alerts?

    Does Excel offer a way to alert you of a change in data?

    I am responsible for the tracking and maintenance of our fleet of vehicles.
    I would like to know if there is ANY way to set-up a spreadsheet so that
    every 3 months or 3,000 miles, Excel will let me know that the vehicle is
    ready for Preventative Maintenance service.

    Any suggestions? I hope I'm not the only one who has every been curious
    about this.

    I have already tried to work a bit with conditional formatting but not with
    much success.

  2. #2
    Bernard Liengme
    Guest

    Re: Alerts?

    Let A1 have the vehicle's last PM date
    In another cell use =IF(TODAY()-A1>90,"PM needed","")
    Or use this formula in a conditional format (Format|Conditional Format) to
    make the row red.
    If B1 has mileage of last PM and B2 has current mileage =IF(B2-B1>2999,"PM
    needed","") will work
    Also =IF(OR(TODAY()-A1>90, B2-B1>2999),"PM needed","") will cover both
    best wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "audiophile" <audiophile@discussions.microsoft.com> wrote in message
    news:DD035B46-F1BA-4637-B111-326EFB032474@microsoft.com...
    > Does Excel offer a way to alert you of a change in data?
    >
    > I am responsible for the tracking and maintenance of our fleet of
    > vehicles.
    > I would like to know if there is ANY way to set-up a spreadsheet so that
    > every 3 months or 3,000 miles, Excel will let me know that the vehicle is
    > ready for Preventative Maintenance service.
    >
    > Any suggestions? I hope I'm not the only one who has every been curious
    > about this.
    >
    > I have already tried to work a bit with conditional formatting but not
    > with
    > much success.




  3. #3
    Registered User
    Join Date
    12-28-2005
    Posts
    38
    Do you track the mileage daily? If you don't keep track of the mileage somehow, to where excel knows the vehicle has reached over 3000 miles in an interval, it won't work exactly, you would be estimating daily mileage, and that's never worthwhile IMO.

    My suggestion:

    Create a column for last date of service for the vehicle, and the mileage at the time of last service, current mileage, and obviously a vehicle identifier of some sort. If you update this daily, then have one column that holds the current date, and change it daily (I do this because it helps me remember if I had completed this report or not, since I do many each day). The formulas above would work as well.

    Place your conditional format on both the date and mileage fields as follows:

    For date:
    If cell value (of last date of service) - is less than - (current date cell) minus 90, and format as you please.

    For mileage:

    If cell value (of current mileage) is greater than - (mileage at last service) + 3000, then format as you wish.

    Let me know if that would be satisfactory.
    Last edited by darkwood; 12-28-2005 at 04:54 PM.

  4. #4
    audiophile
    Guest

    Re: Alerts?

    Thanks for the help! FYI, as furnished by Bernard, there is a formula that
    holds the current date; TODAY().

    "darkwood" wrote:

    >
    > Do you track the mileage daily? If you don't keep track of the mileage
    > somehow, to where excel knows the vehicle has reached over 6000 miles
    > in an interval, it won't work exactly, you would be estimating daily
    > mileage, and that's never worthwhile IMO.
    >
    > My suggestion:
    >
    > Create a column for last date of service for the vehicle, and the
    > mileage at the time of last service, current mileage, and obviously a
    > vehicle identifier of some sort. If you update this daily, then have
    > one column that holds the current date, and change it daily. (there may
    > be a formula that holds current date, but I am not positive)
    >
    > Place your conditional format on both the date and mileage fields as
    > follows:
    >
    > For date:
    > If cell value (of last date of service) - is less than - (current date
    > cell) minus 90, and format as you please.
    >
    > For mileage:
    >
    > If cell value (of current mileage) is greater than - (mileage at last
    > service) + 3000, then format as you wish.
    >
    > Let me know if that would be satisfactory.
    >
    >
    > --
    > darkwood
    > ------------------------------------------------------------------------
    > darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
    > View this thread: http://www.excelforum.com/showthread...hreadid=496482
    >
    >


  5. #5
    audiophile
    Guest

    Re: Alerts?

    Wow! You really know your stuff! Thanks for the help-this worked perfectly.

    "Bernard Liengme" wrote:

    > Let A1 have the vehicle's last PM date
    > In another cell use =IF(TODAY()-A1>90,"PM needed","")
    > Or use this formula in a conditional format (Format|Conditional Format) to
    > make the row red.
    > If B1 has mileage of last PM and B2 has current mileage =IF(B2-B1>2999,"PM
    > needed","") will work
    > Also =IF(OR(TODAY()-A1>90, B2-B1>2999),"PM needed","") will cover both
    > best wishes
    > --
    > Bernard V Liengme
    > www.stfx.ca/people/bliengme
    > remove caps from email
    >
    > "audiophile" <audiophile@discussions.microsoft.com> wrote in message
    > news:DD035B46-F1BA-4637-B111-326EFB032474@microsoft.com...
    > > Does Excel offer a way to alert you of a change in data?
    > >
    > > I am responsible for the tracking and maintenance of our fleet of
    > > vehicles.
    > > I would like to know if there is ANY way to set-up a spreadsheet so that
    > > every 3 months or 3,000 miles, Excel will let me know that the vehicle is
    > > ready for Preventative Maintenance service.
    > >
    > > Any suggestions? I hope I'm not the only one who has every been curious
    > > about this.
    > >
    > > I have already tried to work a bit with conditional formatting but not
    > > with
    > > much success.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1