Vehicle mileage: pre-trip and post-trip mileage, auto calculating multiple rows


I've created a new system to help out the finance department at my workplace to be able to pull in multiple types of information. The part I am hung up on involves vehicle mileage.

The user will enter all the required information via a smartsheet form to replace the paper forms. This is what info I need for every trip:

-the type of charge (vehicle mileage; auto fills via a hidden field on the form)

-the date (auto fills with created date)

-the name of the PI/lab

-a description of the trip

-the account/fund information "project number"

-the vehicle number

---user selects "mileage type" on the form to determine whether they will see "starting" or "ending" mileage fields---

-the starting mileage

-the ending mileage

Ultimately, I want the sheet to auto-calculate the TOTAL mileage for each trip, so that I can then use that mileage to auto-calculate how much should be charged to the PI/lab account listed.

Initially, I thought that at the end of each trip, the user would enter all of this information at once. However, in discussing with the finance crew here we realized that this would require the user to make a note somewhere of their starting mileage to save for later so that when they get back they can enter both the starting and ending mileage. It was suggested that people make two form entries when checking out a vehicle - the first entry for starting mileage, the second entry for ending mileage. However, this creates two rows so I can't as easily use the sheet to auto-calculate the total mileage. (There are multiple vehicles so there could easily be rows in between so it's not necessarily going to be the case that the pre-trip and post-trip row for one trip will be adjacent.)

The goal is to simplify the process for EVERYONE. My options seem problematic:

  1. Ask users to write down starting mileage somewhere so that they have it to make their post-trip log. Why bother digitizing if they are still having to use paper?
  2. Ask users to make one pre-trip and one post-trip entry. This means they have to do two steps, and also means that finance will have to calculate by hand on the back end.

If it's not going to be easier than using paper, it's not going to fly. Any suggestions?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!