If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Alerts?
Let A1 have the vehicle's last PM date
In another cell use =IF(TODAY()-A190,"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-B12999,"PM needed","") will work Also =IF(OR(TODAY()-A190, B2-B12999),"PM needed","") will cover both best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "audiophile" wrote in message news 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
|
|||
|
|||
Alerts?
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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()-A190,"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-B12999,"PM needed","") will work Also =IF(OR(TODAY()-A190, B2-B12999),"PM needed","") will cover both best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "audiophile" wrote in message news 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Desktop Alerts for Domino Connector | Steve Henry | Installation & Setup | 0 | June 23rd, 2005 09:49 PM |
SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS 2 | Dr. Tulip | Powerpoint | 1 | May 16th, 2005 04:37 AM |
SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS -- SCAM ALERTS -- | Dr. Tulip | Powerpoint | 0 | May 15th, 2005 04:02 AM |
Outlook 2003 Rules and Alerts are completely screwed up... | Ken Colasuonno | General Discussion | 5 | May 12th, 2005 12:07 AM |
Outlook will not open the rules and alerts window | Diane Poremsky [MVP] | General Discussion | 0 | January 14th, 2005 04:16 AM |