Volume visualization for a truck load planner

[PuntingJawa]

I'm attempting a visual sheet for volume (Semi/Box truck capacity). I'm hoping this is possible as my aspirations may exceed what excel is capable of, or that there may be a function that can do this for me already that I have not learned yet. As always, I am sure that I am over complicating it.

Below is where Data would be pulled from.
What I am attempting is the following:
B4-B18, C4-C18, D4-D18 is where an "ID" is placed. This is an ID that is set by the user from column O2# via typing or dropdown menu.

Load planner personal project.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Truck 1													Packaging	ID	Length	Width	Height
2	Floor				Mid				Top				Max height 108 inches	Crate	1	42	42	42
3	Width	ID HERE		3rd row	Width	ID HERE		3rd row	Width	ID HERE		3rd row	Height	Crate	2	43	43	43
4		1	1										42 inches of 108 used	Crate	3	44	44	44
5		1	1										42 inches of 108 used	Crate	4	45	45	45
6		1	1										42 inches of 108 used	Crate	5	46	46	46
7		1	1										42 inches of 108 used	Crate	6	47	47	47
8		1	1										42 inches of 108 used	Crate	7	48	48	48
9		1	1										42 inches of 108 used	Crate	8	49	49	49
10		1	1										42 inches of 108 used	Crate	9	50	50	50
11		1	1										42 inches of 108 used	Crate	10	51	51	51
12		1	1										42 inches of 108 used
13		1	1										42 inches of 108 used
14		1	1										42 inches of 108 used
15		1	1										42 inches of 108 used
16		1	1										42 inches of 108 used
17		1	1										42 inches of 108 used
18	Too wide->	1	1	1		1				1			Too tall! Remove Top/Mid
19		^	^
20		Too Long				Good length				Good length
21		Length used				Length used				Length used
22		630	630	42		42	0	0		42	0	0
23		Length Left				Length Left				Length Left
24		-6	-6	582		582	624	624		582	624	624
Input

Cell Formulas
Range	Formula
M2	M2	="Max height "&$W$13&" inches"
A4:A18	A4	=IF(AU4>$W$11,"Too wide->","")
E4:E18	E4	=IF(AU19>$W$11,"Too wide->","")
I4:I18	I4	=IF(AU34>$W$11,"Too wide->","")
M4:M18	M4	=IF(AV3>$W$13,"Too tall! Remove Top/Mid",AV3&" inches "&"of "&$W$13&" used")
B19	B19	=IF(B22<W9,"","^")
C19	C19	=IF(C22<W9,"","^")
D19	D19	=IF(D22<W9,"","^")
B20,J20,F20	B20	=IF(B19=CHAR(94),"Too Long",IF(C19=CHAR(94),"Too long",IF(D19=CHAR(94),"Too Long","Good length")))
F19	F19	=IF(F22<W9,"","^")
G19	G19	=IF(G22<W9,"","^")
H19	H19	=IF(H22<W9,"","^")
J19	J19	=IF(J22<W9,"","^")
K19	K19	=IF(K22<W9,"","^")
L19	L19	=IF(L22<W9,"","^")
B22	B22	=AY3
C22	C22	=AY4
D22	D22	=AY5
F22	F22	=AY7
G22	G22	=AY8
H22	H22	=AY9
J22	J22	=AY11
K22	K22	=AY12
L22	L22	=AY13
B24	B24	=W9-B22
C24	C24	=W9-C22
D24	D24	=W9-D22
F24	F24	=W9-F22
G24	G24	=W9-G22
H24	H24	=W9-H22
J24	J24	=W9-J22
K24	K24	=W9-K22
L24	L24	=W9-L22

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J19:L19	Cell Value	=""	text	NO
F19:H19	Cell Value	=""	text	NO
B19:D19	Cell Value	=""	text	NO
J20	Cell Value	contains "Good length"	text	NO
J20	Cell Value	contains "Too long"	text	NO
F20	Cell Value	contains "Good length"	text	NO
F20	Cell Value	contains "Too long"	text	NO
J19:L19	Cell Value	contains "^"	text	NO
F19:H19	Cell Value	contains "^"	text	NO
B19:D19	Cell Value	contains "^"	text	NO
B20	Cell Value	contains "Good length"	text	NO
B20	Cell Value	contains "Too long"	text	NO
J24:L24	Cell Value	>0	text	NO
J24:L24	Cell Value	<0	text	NO
F24:H24	Cell Value	>0	text	NO
F24:H24	Cell Value	<0	text	NO
B24:D24	Cell Value	>0	text	NO
B24:D24	Cell Value	<0	text	NO
J23	Cell Value	<0	text	NO
F23	Cell Value	<0	text	NO
B23	Cell Value	<0	text	NO
F21	Cell Value	<0	text	NO
A4:A18,E4:E18,I4:I18,E29:E43,I29:I43,E54:E68,I54:I68,E79:E93,I79:I93,M4:M18,M29:M43,M54:M68,M79:M93	Cell Value	contains "Too"	text	NO
B21,J21	Cell Value	<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B4:D18	List	=$O$2:$O$11
F4:H18	List	=$O$2:$O$11
J4:L18	List	=$O$2:$O$11

What I want to try is if there is an ID from column O in the above ranged cells (B4-18/C4/18/D4-18) that it'll reference column P (Length) and Q (Width) for the data required.
In this example B4 has a 1.
I would like to add it to a graph of sorts below (Or another function if easier and more viable). Each cell in the graph represents an inch.
ID 1 has a length of 42 (Column P) and width of 42 (Column Q).
I would like these to fill in based on the above information.
In this case 42 cells from left to right for the width and 42 spaces from top to bottom.
It would process B4, then C4, then D4. After which B5,C5,D5, and so on repeating until we finish B18, C18, and D18.

If this is too complicated or there is a better/easier way to accomplish what I am attempting, please let me know. Honestly, this is just a "fun" function I am trying to add and is more or less a toy at the moment.

As always, thank you MrExcel board. You've taught me so much so far.

 

[Rijnsent]

Mmm, nice challenge. I've been puzzling around with formulas a bit, but guess you'd have to fall back to some VBA to get this done easily. There will be space between the boxes etc. If not, these functions might help you:

=XLOOKUP(B4,$O$2:$O$10,$P$2:$P$10,0,0)

-> will lookup the length of a crate for a certain ID (in cell B4)

=COLUMN()

-> will give back the column number

=ROW()

-> will give back the row number
If I focus purely on "length" -> I'd first make a "cumulative table", so using the XLOOKUP to find the length per ID and then adding them up, in your example you'd get a list: 42, 84, 126, etc. With your L/W table: use

=XMATCH(64,Input!$B$54:$B$68,1)

-> the second item in that formula is the cumulative table. It'll lookup 64 in the list 42,84, etc and will give back "2", your second item. You can imagine using that in your last sheet to drag down and get per cell the ID/row it belongs to.

As said, that's only a start and I think you'd have to fall back to VBA to get this done. It might be a good starting point for learning it though .

 

[PuntingJawa]

I've never used xmatch before so it'll be a fun function to play with. Thanks for the advice! I'm worried about the impact it may have on the sheet and if i were to go down with the route i visualize in my head. The approach may be overly complicated. There's quite a lot of data to consider with calculation of volumes utilized versus used. I've also looked into the graphs to see if I can use that instead. I'm just having issues with locking the data in properly.

In a nutshell, I'm just trying to give a visualization of what's used and where vs what's available and why an item would or would not fit. From what I can see, there is nothing excel based out there that comes close to what I've made, or there is only paid applications that are overly complicated that have a massive learning curve and price tag. If I can get this to work, I'm going to post it so it's freely available.


Again, thank you for giving me more to work with. I'll see what I can come up with.

 

[Rijnsent]

Yeah, you've got a challenge on your hands... See this example: I created an area of 8 wide, 18 long. I first added 1 box of 5x5 (grey) and one box of 3x3 (red, both fit), if you afterwards want to add a box of 5x5, you can place that at the right, but you'll have some empty space. So it's quite a challenge to fill it all up .

 

[PuntingJawa]

Yeah, you've got a challenge on your hands... See this example: I created an area of 8 wide, 18 long. I first added 1 box of 5x5 (grey) and one box of 3x3 (red, both fit), if you afterwards want to add a box of 5x5, you can place that at the right, but you'll have some empty space. So it's quite a challenge to fill it all up .

View attachment 107152

That is actually what I'm trying to show. With truck loads, you never quite use 100% of space. There will always be some left. As long as I can get it to look like what it should look like visually, then it's the right direction

 

[AhoyNC]

It may not matter with your product, but you may also need to consider weight distribution and can the product be stacked.

 

[PuntingJawa]

It may not matter with your product, but you may also need to consider weight distribution and can the product be stacked.

Agreed. For now I'm just trying to get the template together for a visual. Most formula are in the first sheet which is the manual input portion that I'll be pulling information from. Weight distribution is simple enough to factor in as (keep in mind that I haven't had to worry about this in a few years) the first 1/3 of the truck (nose) can handle 12,000 to 14,000 lbs and the other 2/3, 32,000. Reefer trucks should subtract up to 250-350 lbs for the fridge unit. This is subject to type of trailer as well. Some can't move their tandems which complicates it a bit more. All this can be added as warnings later. As for items that are stackable. I already have warnings in place of too tall. Items that shouldn't stack due to being too weak would be more of a shipper experience thing.
Did I give enough information or is there any other information that would help? I have a habit of giving more information than required and tend to confuse even myself sometimes.

 

[AhoyNC]

Its not Excel but, You may want to look at this website to see if it helps.
I think it might be too complicated for Excel to handle. If you search this website and google for container volume calculations you will find a number of posts with people trying to do this in Excel without much luck.

https://www.pier2pier.com/loadcalc/cargo

 

[PuntingJawa]

Its not Excel but, You may want to look at this website to see if it helps.
I think it might be too complicated for Excel to handle. If you search this website and google for container volume calculations you will find a number of posts with people trying to do this in Excel without much luck.

https://www.pier2pier.com/loadcalc/cargo

It's that site which got me thinking about it and with how complicated it was, got me making my own.
I already have the base for it. This isn't automated for the truck load as the user would still need to place ID's in, but it's a convenient tool no less. As of now, I have the base for it. Just want a visual and that is what I am working toward.