I apologize if the above Post Topic Title is weird or misleading. I don't really know how to succinctly identify my need in a single line of text. But here is what I'm trying to do:
I have a monthly expense report worksheet that tracks gas receipts and odometer readings at the time of each gas fill-up. My goal is to extrapolate from the worksheet a way to itemize how many miles were driven per expense report for each of the vehicles included in the report.
(Background: We have one person that is responsible for filling up all the company's trucks and he has to fill out an expense report each month that includes not only the gas receipts but all other job-related receipts that he is involved in. We also want to track the mileage driven during that month for each of our vehicles. So he fills out an expense report that includes the date, 'purchased from', the cost, the vehicle filled up (via a license plate number), and the odometer reading at the time of the fill up.)
So let's say I have 2 columns with 10 rows each. In Column A each of the cells has a data validation drop-down list that requires the user to select a license plate number that corresponds to the vehicle that was just filled up at the gas station. There are 3 possible license plates numbers from which to choose that correspond to 3 separate vehicles.
In Column B, the user is to enter the odometer reading of the vehicle referenced in Column A at the time the vehicle was filled with gas.
My goal is to have a place at the bottom of the worksheet that can look at Column B and determine the mileage driven for each of the 3 vehicles (based on the license plate number selected in Column A) and then return the result of that calculation. So at the end, I can have separate cells that shows that, for example, Vehicle 1 was driven 500 miles during the month, Vehicle, 2 was driven 250 miles during the month, etc. This may need to expand to up to 8 vehicles.
The difficulty is that the vehicles selected in Column A will not necessarily be consecutive and will often occur in the expense report multiple times. (The same vehicle may be filled up 2 or 3 times during the month.) For example, on day 1, it may have Vehicle 1 with an odometer reading of 5,000 and then on day 2 Vehicle 2 is listed with an odometer reading of 7,500. Then on day 6 and 10, there may be receipts and odometer readings for Vehicle 1 again, with Vehicle 3's information and other non-vehicle receipts being filled in for the other days. So without the selected data sets being consecutive, I can't figure out a way to accomplish what I'm trying to do.
Is there a way to perform such a calculation so that Excel can determine the specific vehicle by what is selected in Column A and then based on the corresponding odometer readings in Column B provide me with a Total Miles Driven for the month (or, per report) for each vehicle included even though the vehicles listed are not listed consecutively and may be listed multiple times?
I hope I explained this well enough. Please advise if clarification is needed. Thank you for your help.
I have a monthly expense report worksheet that tracks gas receipts and odometer readings at the time of each gas fill-up. My goal is to extrapolate from the worksheet a way to itemize how many miles were driven per expense report for each of the vehicles included in the report.
(Background: We have one person that is responsible for filling up all the company's trucks and he has to fill out an expense report each month that includes not only the gas receipts but all other job-related receipts that he is involved in. We also want to track the mileage driven during that month for each of our vehicles. So he fills out an expense report that includes the date, 'purchased from', the cost, the vehicle filled up (via a license plate number), and the odometer reading at the time of the fill up.)
So let's say I have 2 columns with 10 rows each. In Column A each of the cells has a data validation drop-down list that requires the user to select a license plate number that corresponds to the vehicle that was just filled up at the gas station. There are 3 possible license plates numbers from which to choose that correspond to 3 separate vehicles.
In Column B, the user is to enter the odometer reading of the vehicle referenced in Column A at the time the vehicle was filled with gas.
My goal is to have a place at the bottom of the worksheet that can look at Column B and determine the mileage driven for each of the 3 vehicles (based on the license plate number selected in Column A) and then return the result of that calculation. So at the end, I can have separate cells that shows that, for example, Vehicle 1 was driven 500 miles during the month, Vehicle, 2 was driven 250 miles during the month, etc. This may need to expand to up to 8 vehicles.
The difficulty is that the vehicles selected in Column A will not necessarily be consecutive and will often occur in the expense report multiple times. (The same vehicle may be filled up 2 or 3 times during the month.) For example, on day 1, it may have Vehicle 1 with an odometer reading of 5,000 and then on day 2 Vehicle 2 is listed with an odometer reading of 7,500. Then on day 6 and 10, there may be receipts and odometer readings for Vehicle 1 again, with Vehicle 3's information and other non-vehicle receipts being filled in for the other days. So without the selected data sets being consecutive, I can't figure out a way to accomplish what I'm trying to do.
Is there a way to perform such a calculation so that Excel can determine the specific vehicle by what is selected in Column A and then based on the corresponding odometer readings in Column B provide me with a Total Miles Driven for the month (or, per report) for each vehicle included even though the vehicles listed are not listed consecutively and may be listed multiple times?
I hope I explained this well enough. Please advise if clarification is needed. Thank you for your help.
