Accountant Needing Help Allocating Expenses Across Multiple Columns

[ripdaddy50]

I am trying to allocate expenses across multiple columns (with the push of a button using macros) and divide the total expense by the amount of trucks that the expense is allocated to. We have a fleet of 11 trucks and will have an expense item that needs to be separated between a certain number of trucks, but all trucks may not get an allocation for a certain account item. I have put an example of what the report would look like with the keys to success for the report being that our guys can one-click to allocate and the sheet is able to divide the $ amount by the number of times that the row is clicked.

We would like for the crew leads out in the field to be able to just click on column "T-1", "T-4" and "T-7" and quickly allocate his expenses for supplies across those three trucks. The $ amount would be divided by the number of trucks that the expense is allocated to automatically when he clicks on the cell to allocate to a particular truck.

Any help that you all could give would be greatly appreciated. Our report includes hundreds of expenses per week and I need to make it as easy on my guys out in the field as possible.

$ Amount	Invoice #	Account	T - 1	T - 2	T - 3	T - 4	T - 5	T - 6	T - 7
74.52	1	Fuel
77.22	2	Supplies
81.92	3	Equip
69.50	4	Uniform
76.31	5	Postage

<tbody>
</tbody>

Hope that this makes sense. Please let me know if you have any questions.

 

[oldbrewer]

$ Amount	Invoice #	Account	T - 1	allocationT1	T - 2	allocationT2	T - 3	allocationT3	T - 4	allocationT4	T - 5	allocationT5	T - 6	allocationT6	T - 7	allocationT7	tot allocs
74.52	1	Fuel	1	18.63	1	18.63			1	18.63	1	18.63					4
77.22	2	Supplies	1	25.74			1	25.74							1	25.74	3
81.92	3	Equip											1	81.92			1
69.5	4	Uniform					1	69.50									1
76.31	5	Postage											1	38.16	1	38.16	2
		I have added a column for field people to put a one in
		col R totals these ones
		and the allocation columns apportionit in the correct locations
		formula in E2 (18.63)
		=IF(D2="","",$A2/$R2)

<colgroup><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[ripdaddy50]

Thank you so much for you help! The formula and layout works great.

Do you know of an easy way to make it so that the guys can simply click in the cell to add the "1" instead of typing it? I would like to make it so that all the cells are locked except for the one that they can click and that the entry is a simple click. These guys are not very computer savvy and we are trying to take away as much of the potential for user error as possible.

Thanks

 

[oldbrewer]

you can lock every cell on the spreadsheet EXCEPT for where they are allowed to type 1 - look in help for protect cells