Good Day excellians.
So I am making a spreadsheet to attempt to make our fundraising activities more efficient and profitable. However, I have hit another wall in regard to facilitating an auto-calculating, auto updating fuel cost calculator. It gets quite complicated...
test workbook.xlsx
'Overview':
The table shows fuel prices per mile, based on fuel type, engine CC and date. The fuel prices are based on government advisory costs which change quarterly. I would like to be able to add new entries in every quarter in a new row, and have the current fuel cost update based on the current date.
'Accounts':
This is where information is entered upon the completion of our fundraising, with ever two columns constituting one teams entry. The rows in red in column A are the only significant entries for this problem. Notice the "fundraiser" and Driver" rows have drop down lists for the drivers names.
'Drivers':
This is where the drivers details are stored, including their engine cc and fuel type. Notice that column A is populated based on the criteria in column E of 'Active' or 'Inactive'. This populates the drop-down list in the 'Accounts' sheet.
PRIMARY PROBLEM:
The issue that is perplexing me is how to calculate the fuel cost in the sheet 'Accounts', row 28.
It should calculate the total cost of fuel based on the mileage (Accounts, R5), Driver Name and correlating engine CC+Fuel type, the correlating Cost-per-Mile (Overview Table), all ultimately dependent on the date.
As the fuel prices would be updated in new rows on the fuel price table, the formula would need to change based on dates.
I have been struggling to find a solution but its perplexed me. Perhaps I am misguided and there is a simpler solution? Any help would be enormously appreciated!!
(Minor Problem):
When setting up the dropdown lists, I check ignore blanks in the data validation box, but the dropdown still has blanks. Anyone know why?
Thanks for reading, and thanks in advance to anyone who may be able to help!
Bookmarks