I have a sheet where my goal is to remind users when they need a brake change, oil change, tire rotations, etc.
In one column I have the “reminder type,” which tells of what they want to be reminded of and the recommended miles it needs to be changed (25k-50k miles, etc.)
In another column I have the date they last completed the brake change, oil change, tire rotations, etc.
Finally, I have the column where they tell me how much driving they do (10-40 miles/day).
What I need: If a person drives 10-40 miles per day (column H) and they select “brake change” (column E) and tell me they last changed it 9/21/19 (column F), I need the “Reminder date” (column G) to automatically calculate.
Here’s the math:
40 miles/day x 7 (days) x 4 (weeks) x 24 (months) = 26,880 miles driven. Therefore they’ll get a text reminder saying they should get a brake change. This math would carry on for any of the other reminders.
I copied your sheet, made minor modifications to Reminder Types, and put in a formula on the Reminders tab. Take a look and see if that gets you closer. I’m just using the minimum value for the brake change and just put in a default of 40 for the ‘Do you travel much’ column. Maybe you want to do something similar to a VLOOKUP for the miles per day, or maybe you want to use the average of the reminder type range and the miles per day range. I guess that’s up to you. Hopefully this helps you out.
Jeff, this is awesome! How can this be copied for all other ones? Let me know if I can pay you to complete this for me. It might be worth a phone call. Let me know.
I made more changes to populate the other rows. I reworked Cars, Reminders and Reminder Types and added a Driving Habits Sheet. I am using the average value for the mileage and month ranges. There is a lot going on with all of the formulas that would be hard to explain, but take a look and play with it for awhile. All formulas are ArrayFormulas and built into row 1 of each sheet.
I try to just stick to the forum. This is just a side hobby as I have free time. All of the formulas are sustainable as in, you can keep adding more to your reminders sheet and you can add items to your choice sheets and as long as the lookups can find a matching value, they will return the proper values to perform the calculations. I think it would be best if you copy and play around with the formulas to see how they work. Once you figure them out, it will help when building formulas elsewhere in your sheets. I’m sure a lot of the design and flow will change as you progress.
