Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2005

Tip: Looking for answers? Try searching our database.

Alerts?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
audiophile - 28 Dec 2005 19:45 GMT
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.
Bernard Liengme - 28 Dec 2005 20:29 GMT
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
Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Does Excel offer a way to alert you of a change in data?
>
[quoted text clipped - 10 lines]
> with
> much success.
audiophile - 29 Dec 2005 14:16 GMT
Wow! You really know your stuff! Thanks for the help-this worked perfectly.

> Let A1 have the vehicle's last PM date
> In another cell use =IF(TODAY()-A1>90,"PM needed","")
[quoted text clipped - 18 lines]
> > with
> > much success.
darkwood - 28 Dec 2005 20:49 GMT
Do you track the mileage daily?  If you don't keep track of the mileag
somehow, to where excel knows the vehicle has reached over 6000 mile
in an interval, it won't work exactly, you would be estimating dail
mileage, and that's never worthwhile IMO.  

My suggestion:

Create a column for last date of service for the vehicle, and th
mileage at the time of last service, current mileage, and obviously
vehicle identifier of some sort.  If you update this daily, then hav
one column that holds the current date, and change it daily. (there ma
be a formula that holds current date, but I am not positive)

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

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

For mileage:

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

Let me know if that would be satisfactory
audiophile - 29 Dec 2005 14:16 GMT
Thanks for the help! FYI, as furnished by Bernard, there is a formula that
holds the current date; TODAY().

> 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
[quoted text clipped - 22 lines]
>
> Let me know if that would be satisfactory.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.