reminder to change oil after 5000 miles with multiple vehicles on the same sheet

Joseph Schenone

I have a spreadsheet for truck maintenance on my vehicle fleet of about 15 vehicles, and I am trying to figure out a formula or some way to send a reminder email if the truck has accumulated 5000 miles since it's last oil change. I have columns for the date, the truck I.D., a checkbox for if the oil was changed that day, and mileage.

 

Any ideas?

Shaine Greenwood

Hi Joseph,

Instead of having a checkbox, you might consider having another text/number column that has "mileage since at last oil change."

Then use your other text/number column with the current mileage as something you could update periodically. You might even consider sending update requests to your drivers to get them to update the current mileage: https://help.smartsheet.com/articles/504779-using-update-requests

You could then have a third column with a flag or checkbox, and put an IF statement in it that takes the difference between "current mileage" and "mileage at last oil change" and checks/flags when that is greater than or equal to 5000. 

You could then create a notification rule that sends you an email when the "needs oil change?" box is checked.

I've got screenshots of this below.