Truck load capacity

[PuntingJawa]

Good afternoon MrExcel board!

I'm trying to make an excel sheet to create load capacity for shipments. I have started with basics but am having trouble bringing this to the next level. The main issue I am having is figuring out how many crates need to be placed on which row and how many can fit. There are 2 items that can only be placed on the bottom row and 3 items that can be placed on any of the 3 rows. I feel like I am on the right path but don't know how to go about working it.

I'll explain what I am attempting.

I want E12-E16, G14-G16, and I14-I16 to show how many crates can fit per row with length used.

A few issues I have a problem with are, I don't know how to formulate the above mentioned to allow use of 636. Example In D15 where we go into negative there are 314 inches left. If divided by D8 (length of DO crate) it comes to 4.8. Since we can't use a fraction of a location the .8 is gone. I would need it to reflect that. Also, Each row can hold 2 crates. I haven't figured out how to do 1/2 rows yet.
The next issue is F14-F16 and I14-I16. Each "Row" has only 636 inches. So my current formula won't work once the above is figured out. I would need to remove the length of crates already used.

Any help with this would be amazing. Please let me know if a better explantion is required. I'll have the board up when I return home and still continue to work on this on my own for now.

Truck load template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4			Type / Max Stack	Length	Width	Height	Weight	QTY	Total (LBS)	Total Inch		Notes
5			Box Crate (1x Max)	42	57	43	850	6	5100	252		636" Per "Lane"
6			Car Kit (1x Max)	44	44	81	250	0	0	0		1xRow = 2x "Lane"
7			EDO Crate (3x Max)	70	37	25	450	1	450	70		1,272" Both Lanes
8			DO Crate (3x Max)	65	37	25	750	24	18000	1560		2,544" If Stacked
9	LE QTY	240	LE Pallet (3x Max)	96	48	16	960	1	960	96		32,000 (Max LBS set)
10												Actual Max 34k LBS
11			Load plan	1 Row	QTY 1	2 Row	QTY 2	3 row	QTY 3	Stackable		Based on 53'x8.2'x8'
12			Box Crate (1x Max)	384		1020		2292		No		636x98.4x96 inches
13			Car Kit (1x Max)	384		1020		2292		No
14			EDO Crate (3x Max)	314		950		2222		Yes
15			DO Crate (3x Max)	-1246		-610		662		Yes
16			LE Pallet (3x Max)	-1342		-706		566		Yes
17
18			Total Weight in LBS
19			24510
Sheet1

Cell Formulas
Range	Formula
I5:I9	I5	=H5*G5
J5:J9	J5	=D5*H5
G9	G9	=B9*4
D12	D12	=636-J5
D13:D16	D13	=D12-J6
F12	F12	=1272-J5
F13:F16	F13	=F12-J6
H12	H12	=2544-J5
H13:H16	H13	=H12-J6
C19	C19	=I9+I5+I7+I8+I6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12:I16	Cell Value	<0	text	NO
F12:G16	Cell Value	<0	text	NO
D12:E16	Cell Value	<0	text	NO
H12:I16	Cell Value	between 0 and 2544	text	NO
F12:G16	Cell Value	between 0 and 1272	text	NO
D12:E16	Cell Value	between 0 and 636	text	NO
C19	Cell Value	<32000	text	NO
C19	Cell Value	>32000	text	NO

 

[PuntingJawa]

one of two is fine for me.
you can decide it.
Thank you so much. I own you a big thanks.

No problem. I'll have something with a drop down menu to choose between metric and standard this weekend. I don't think that it will be difficult to work out in.

 

[ngochien251088]

Yeah,
I am looking forward to good news from you.
But just help me if you have free time.
THank you and sorry again.

 

[PuntingJawa]

Changelog:
Added dropdown on input page for inch/cm

Effects of each for input and bill of lading:
"inch" will use imperial and LBS
"cm" will utilize metric and KG

Other improvements:
Fixed a barely noticeable stutter when using BOL auto hide vba.
     Issue was caused due to too much information on one sheet. Simply moved data to a helper sheet.

As always, live updates are always made.

Tasks I'm still trying to accomplish:
Creation of a usable axle weight calculator.
Issue: too many variables. I require more data on multiple types of trucks, trailers, and their capacities. As of now, I only have estimates based on on set of data.

Yeah,
I am looking forward to good news from you.
But just help me if you have free time.
THank you and sorry again.

Please see the new update and let me know if there is anything else.

Load planner personal project.xlsm

Truck load planner for logistics freight.

drive.google.com

 

[ngochien251088]

Changelog:
Added dropdown on input page for inch/cm

Effects of each for input and bill of lading:
"inch" will use imperial and LBS
"cm" will utilize metric and KG

Other improvements:
Fixed a barely noticeable stutter when using BOL auto hide vba.
     Issue was caused due to too much information on one sheet. Simply moved data to a helper sheet.

As always, live updates are always made.

Tasks I'm still trying to accomplish:
Creation of a usable axle weight calculator.
Issue: too many variables. I require more data on multiple types of trucks, trailers, and their capacities. As of now, I only have estimates based on on set of data.

Please see the new update and let me know if there is anything else.

Load planner personal project.xlsm

Truck load planner for logistics freight.

drive.google.com

Many thanks for your feedback.
I will try the file and come back to you soon.
Thank you and wish you have a nice week!

 

[Joe4]

PuntingJawa,

I have "unmarked" the post with your latest questions.
Please do not mark a post as the solution unless it actually contains the solution to your original problem.