Logistical cost, Table Design with Functions

[Jithe]

Hi Guys,

I need help finishing a table design with functions that calculates logistics cost. It has to be simple and easy to use because there are many driver, rates & they work throughout the year (365 days). This means there will be multiple table per excel sheet. Each sheet will be a month.

So far I have designed the following table with functions below,


[TABLE="class: grid, width: 673"]
<tbody>[TR]
[TD]Vehicle Number:
[/TD]
[TD]68-0266
[/TD]
[TD]Pallet Capacity:
[/TD]
[TD="align: center"]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Owner/Driver:
[/TD]
[TD]E. Silva
[/TD]
[TD]Rate/KM:
[/TD]
[TD="align: center"]15.83
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Work Days
[/TD]
[TD="align: center"]1
[/TD]
[TD]Fixed Rate/ Day:
[/TD]
[TD="align: center"]2790
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Distance Traveled:
[/TD]
[TD="align: center"]200
[/TD]
[TD]Grand Total:
[/TD]
[TD="align: center"]5956
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Transport Order Number
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Distance
[/TD]
[TD="align: center"]Total Distance
[/TD]
[TD="align: center"]Trip Total
[/TD]
[/TR]
[TR]
[TD="align: center"]65465165
[/TD]
[TD="align: center"]07/03/17
[/TD]
[TD="align: center"]100.00
[/TD]
[TD="align: center"]200
[/TD]
[TD="align: right"]5,956.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD="align: right"]2,790.00
[/TD]
[/TR]
</tbody>[/TABLE]

Vehicle Number is a drop down list with all the licence plate numbers of the flatbed trucks. When a LP number is selected it will load the Owner/Driver name, Pallet Capacity, Rate/KM & Fixed Rate/ Day. I used a data sheet to do this.

• Owner/Driver name, Pallet Capacity, Rate/KM & Fixed Rate/ Day appear via a vlookup function.
• Total Work Days is found using a counta function. It counts the all the dates in the date column.
• Total Distance Traveled is found using sum of total distance column
• Total Distance is Distance times 2, because of the return trip.
• Trip Total is calculated by [(Total Distance * Rate/KM) + Fixed Rate/ Day]
• Therefore the Grand Total for that month for the particular driver would be

[(Total Distance Traveled * Rate/KM) + (Total Work Days * Fixed Rate/ Day)]​

To complete this table I would like to add a date picker so that the date fills in the cell when dates are clicked on a calendar instead of having to manually type each date. Also I would like if Trip total were to remain blank until a transport order number and/or date were entered.

Additionally I have copied this table at least 20 times per sheet to calculate the cost per truck. However when I do this it tends to break the table, especially when I copy it to the other sheets.

Finally this excel file will be shared with many people (accounts department), so when they rename the file or something I don't want it to stop working.

Please provide additional ideas to make this table simpler and more efficient.
Sorry for the long post, your assistance is greatly appreciated.

Thank you

Regards,
Jithe.

 

[Jithe]

Hi guys,

Since there are no replies. Can someone please tell me at least how to add a date picker? Google sheets have this option, not sure how to do it in excel.

Thank you
Regards,
Jithe.