PuntingJawa
Board Regular
- Joined
- Feb 25, 2021
- Messages
- 158
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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.
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.