Nested formula with multiple criteria

[PuntingJawa]

I'm having a bit of an issue figuring this one out. I've tried multiple ways going about it but I think I have too many arguments for the formula to work as I intend it.
If A1 is 0 and B1 is "Truck 1" then show nothing but if A1 is greater than 0 then C1 " " D1 " " E1
But if A1 is 0 and B1 is "Truck 2" then show nothing but if A1 is greater than 0 then C1 " " D1 " " E1
The above repeating to "Truck 3" and "Truck 4"

Is there a different formula I should try to make this work?

 

[etaf]

seems like if A1 is zero show nothing
=IF( A1=0, "",
and b1 contains truck

=IF( A1=0, "", IF( left(B1,5)="truck", C1&" "&D1&" "&E1 , ""))

what if B1 does not have truck

Book3
	A	B	C	D	E	F
1	12	TRUCK 20	C1	D1	E1	C1 D1 E1
2	0	TRUCK 21	C2	D2	E2
3	14	TRUCK 22	C3	D3	E3	C3 D3 E3
4	15	TRUCK 23	C4	D4	E4	C4 D4 E4
Sheet1

Cell Formulas
Range	Formula
F1:F4	F1	=IF( A1=0, "", IF( LEFT(B1,5)="truck", C1&" "&D1&" "&E1, ""))

 

[PuntingJawa]

I should have explained better, and I do apologize. I'm providing a minisheet now to show what I am trying to do.
The "Truck #" is based on a dropdown helper.
If D2 is the dropdown with "Truck #".
If $D$2 is "Truck 1" and B3 is 0 then return nothing, if greater than 0 then C3"x"D3"x"E3"
If $D$2 is "Truck 2" and B6 is 0 then return nothing, if greater than 0 then C6"x"D6"x"E6"
If $D$2 is "Truck 3" and B9 is 0 then return nothing, if greater than 0 then C9"x"D9"x"E9"
If $D$2 is "Truck 4" and B12 is 0 then return nothing, if greater than 0 then C12"x"D12"x"E12"

Book4
	A	B	C	D	E	F	G	H
1	TRUCK 1 DATA	10500 LBS	Truck used	Truck 2		Dropdown		Textstring
2	ID	TYPE QTY	Length	Width	Height	Truck 1
3	1	4	42	42	42	Truck 2
4	TRUCK 2 DATA	0 LBS				Truck 3
5	ID	TYPE QTY	Length	Width	Height	Truck 4
6	1	0	42	42	42
7	TRUCK 3 DATA	0 LBS
8	ID	TYPE QTY	Length	Width	Height
9	1	0	42	42	42
10	TRUCK 4 DATA	0 LBS
11	ID	TYPE QTY	Length	Width	Height
12	1	0	42	42	42
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=$F$2:$F$6

 

[etaf]

is this the real data - will there only be 4 trucks or do we have to work out the row somehow maybe based the truck number

this is messy and will get worse if more than a few trucks

=IF(OR(AND($D$1="truck 1",B3=0),AND($D$1="truck 2",B6=0), AND($D$1="truck 3",B9=0), AND($D$1="truck 4",B12=0)),"",IF($D$1="truck 1",C3&"x"&D3&"x"&E3,IF($D$1="truck 2",C6&"x"&D6&"x"&E6,IF($D$1="truck 3",C9&"x"&D9&"x"&E9,IF($D$1="truck 4",C12&"x"&D12&"x"&E12,"")))))

what version does this have to work in , you have 2 versions 365 and 2019 - big difference in number of functions available between the 2

i'm sure a much better way - i will have a think , i suspect other members may provide better solutions
maybe using offset , not sure
=OFFSET(A1,2,3)
looking up the truck address, so truck 1 data is in A1

is this going to be the real layout
How many trucks likely to be

Book4
	A	B	C	D	E	F	G	H
1	TRUCK 1 DATA	10500 LBS	Truck used	Truck 1		Dropdown		Textstring
2	ID	TYPE QTY	Length	Width	Height	Truck 1		42x42x42
3	1	4	42	42	42	Truck 2
4	TRUCK 2 DATA	0 LBS				Truck 3
5	ID	TYPE QTY	Length	Width	Height	Truck 4
6	1	0	42	42	42
7	TRUCK 3 DATA	0 LBS
8	ID	TYPE QTY	Length	Width	Height
9	1	0	42	42	42
10	TRUCK 4 DATA	0 LBS
11	ID	TYPE QTY	Length	Width	Height
12	1	0	42	42	42
Sheet1

Cell Formulas
Range	Formula
H2	H2	=IF(OR(AND($D$1="truck 1",B3=0),AND($D$1="truck 2",B6=0), AND($D$1="truck 3",B9=0), AND($D$1="truck 4",B12=0)),"",IF($D$1="truck 1",C3&"x"&D3&"x"&E3,IF($D$1="truck 2",C6&"x"&D6&"x"&E6,IF($D$1="truck 3",C9&"x"&D9&"x"&E9,IF($D$1="truck 4",C12&"x"&D12&"x"&E12,"")))))

 

[PuntingJawa]

is this the real data - will there only be 4 trucks or do we have to work out the row somehow maybe based the truck number

this is messy and will get worse if more than a few trucks

=IF(OR(AND($D$1="truck 1",B3=0),AND($D$1="truck 2",B6=0), AND($D$1="truck 3",B9=0), AND($D$1="truck 4",B12=0)),"",IF($D$1="truck 1",C3&"x"&D3&"x"&E3,IF($D$1="truck 2",C6&"x"&D6&"x"&E6,IF($D$1="truck 3",C9&"x"&D9&"x"&E9,IF($D$1="truck 4",C12&"x"&D12&"x"&E12,"")))))

I am only planning up to 4 trucks total. This has been a massive undertaking for me as a personal project. I am setting this up ahead of time because I plan on introducing a macro that auto hides empty rows based on certain cells in column B being empty and I cannot get it to work unless the data is blank otherwise.

What you have given me has provided a baseline. It worked when I put the real cells to play. This is what it looks like based on the data.

This is what it looks like on truck 2 with no data.

Thank you very much. It's exactly what I needed.

 

[etaf]

you are welcome, glad it worked
as i say , you may get better answers

 

[Asbestos_Jen]

I'm trying to learn some of the new functions using the free excel web app, so I tried the formulas below. I changed the dimensions of the trucks so I could see if the formulas were pulling from the correct rows. The Xlookup uses the layout on the right rather than the layout from post 3.

Textjoin:

=IF(AND(B3>0,$D$1="Truck 1"),TEXTJOIN("x",TRUE,C3,D3,E3),IF(AND(B6>0,$D$1="Truck 2"),TEXTJOIN("x",TRUE,C6,D6,E6),IF(AND(B9>0,$D$1="Truck 3"),TEXTJOIN("x",TRUE,C9,D9,E9),IF(AND(B12>0,$D$1="Truck 4"),TEXTJOIN("x",TRUE,C12,D12,E12),""))))

&:

=IF(AND(B3>0,$D$1="Truck 1"),C3&"x"&D3&"x"&E3,IF(AND(B6>0,$D$1="Truck 2"),C6&"x"&D6&"x"&E6,IF(AND(B9>0,$D$1="Truck 3"),C9&"x"&D9&"x"&E9,IF(AND(B12>0,$D$1="Truck 4"),C12&"x"&D12&"x"&E12,""))))

Xlookup:

=IF(XLOOKUP($D$1,$K$2:$K$5,$N$2:$N$5,"",0,1)=0,"",XLOOKUP($D$1,$K$2:$K$5,(O2:O5&"x"&P2:P5&"x"&Q2:Q5),"",0,1))

TRUCK 1 DATA

10500 LBS

Truck used

Truck 1

Dropdown

Textstring

Truck #

ID

Weight

TYPE QTY

Length

Width

Height

ID

TYPE QTY

Length

Width

Height

Truck 1

42x42x42

Textjoin

Truck 1

1​

10500 LBS

4​

42​

42​

42​

1​

4​

42​

42​

42​

Truck 2

42x42x42

&

Truck 2

1​

0 LBS

0​

43​

43​

43​

TRUCK 2 DATA

0 LBS

Truck 3

42x42x42

Xlookup

Truck 3

1​

0 LBS

0​

44​

44​

44​

ID

TYPE QTY

Length

Width

Height

Truck 4

Truck 4

1​

0 LBS

0​

45​

45​

45​

1​

0​

43​

43​

43​

TRUCK 3 DATA

0 LBS

ID

TYPE QTY

Length

Width

Height

1​

0​

44​

44​

44​

TRUCK 4 DATA

0 LBS

ID

TYPE QTY

Length

Width

Height

1​

0​

45​

45​

45​

 

[PuntingJawa]

I'm trying to learn some of the new functions using the free excel web app, so I tried the formulas below. I changed the dimensions of the trucks so I could see if the formulas were pulling from the correct rows. The Xlookup uses the layout on the right rather than the layout from post 3.

Textjoin:

=IF(AND(B3>0,$D$1="Truck 1"),TEXTJOIN("x",TRUE,C3,D3,E3),IF(AND(B6>0,$D$1="Truck 2"),TEXTJOIN("x",TRUE,C6,D6,E6),IF(AND(B9>0,$D$1="Truck 3"),TEXTJOIN("x",TRUE,C9,D9,E9),IF(AND(B12>0,$D$1="Truck 4"),TEXTJOIN("x",TRUE,C12,D12,E12),""))))

&:

=IF(AND(B3>0,$D$1="Truck 1"),C3&"x"&D3&"x"&E3,IF(AND(B6>0,$D$1="Truck 2"),C6&"x"&D6&"x"&E6,IF(AND(B9>0,$D$1="Truck 3"),C9&"x"&D9&"x"&E9,IF(AND(B12>0,$D$1="Truck 4"),C12&"x"&D12&"x"&E12,""))))

Xlookup:

=IF(XLOOKUP($D$1,$K$2:$K$5,$N$2:$N$5,"",0,1)=0,"",XLOOKUP($D$1,$K$2:$K$5,(O2:O5&"x"&P2:P5&"x"&Q2:Q5),"",0,1))

TRUCK 1 DATA

10500 LBS

Truck used

Truck 1

Dropdown

Textstring

Truck #

ID

Weight

TYPE QTY

Length

Width

Height

ID

TYPE QTY

Length

Width

Height

Truck 1

42x42x42

Textjoin

Truck 1

1​

10500 LBS

4​

42​

42​

42​

1​

4​

42​

42​

42​

Truck 2

42x42x42

&

Truck 2

1​

0 LBS

0​

43​

43​

43​

TRUCK 2 DATA

0 LBS

Truck 3

42x42x42

Xlookup

Truck 3

1​

0 LBS

0​

44​

44​

44​

ID

TYPE QTY

Length

Width

Height

Truck 4

Truck 4

1​

0 LBS

0​

45​

45​

45​

1​

0​

43​

43​

43​

TRUCK 3 DATA

0 LBS

ID

TYPE QTY

Length

Width

Height

1​

0​

44​

44​

44​

TRUCK 4 DATA

0 LBS

ID

TYPE QTY

Length

Width

Height

1​

0​

45​

45​

45​

I've tried the Xlookup before and it felt odd to me. Although long, what you initially gave me works fine. I have no plans on expanding on this in the future. But if someone else does, they can play around. I'll be posting the updated load planner that I am messing with tonight on the original thread. As of right now, it looks like the following.

=IF(OR(AND($R$18="truck 1",Input!BW3=0),AND($R$18="truck 2",Input!CC3=0),  AND($R$18="truck 3",Input!CI3=0), AND($R$18="truck 4",Input!CO3=0)),"",IF($R$18="truck 1",Input!O2&" "&Input!N2&" "&Input!P2&"X"&Input!Q2&"X"&Input!R2,IF($R$18="truck 2",Input!O2&" "&Input!N2&" "&Input!P2&"X"&Input!Q2&"X"&Input!R2,IF($R$18="truck 3",Input!O2&" "&Input!N2&" "&Input!P2&"X"&Input!Q2&"X"&Input!R2,IF($R$18="truck 4",Input!O2&" "&Input!N2&" "&Input!P2&"X"&Input!Q2&"X"&Input!R2,"")))))

It's pretty long since it links to different sheets, but it will do for now. I truly appreciate you having fun and playing around with new functions. I may add them later to clean things up.