I have a cell that I need it to evolve as data is changed, deleted, or added to in the main body of the sheet. (Sheet name "Carton DIM Calculator") I have a very large IF/OR formula that will soon, I suspect, reach a character max point. So before it does that I want to fix it but I'm not sure on where to begin. Also to add to the reasoning I need it fixed is every time I must change information on the sheet I have to go in and change the formula manually. It is time consuming and it's now becoming more and more of a hassle to update. It originally was just a few If/Or's, but our shipping cases have grown from about 10 box sizes to 58 box sizes.
The point of the formula is to return a specified box size based on the dimensions user inputs. Yes, we have a system that does this when a customer places an order and it works 99% flawless on that side. But this side is for setting up new inventory as it comes in the warehouse or when we need to ship something outside of the customer ordering side.
I'm not sure if what I'm currently asking is even achievable or if I will have to manually update this as information changes.
Below is the formula I currently have:
Formula is in Cell F2 Information being called for the formula is between B4 - G4 down to B61 - G61
=IF(OR(B2=0,C2=0,D2=0,B2="",C2="",D2=""),"Pending",
IF(AND((G2<=G4)*(B2<=B4-0.25)*(C2<=C4-0.25)*(D2<=D4)),"#ENV",
IF(AND((G2<=G5)*(B2<=B5-0.25)*(C2<=C5-0.25)*(D2<=D5)),"#99",
IF(AND((G2<=G6)*(B2<=B6-0.25)*(C2<=C6-0.25)*(D2<=D6)),"ENV5",
IF(AND((G2<=G7)*(B2<=B7-0.25)*(C2<=C7-0.25)*(D2<=D7)),"ENV2",
IF(AND((G2<=G8)*(B2<=B8-0.25)*(C2<=C8-0.25)*(D2<=D8)),"ENV3",
IF(AND((G2<=G9)*(B2<=B9-0.25)*(C2<=C9-0.25)*(D2<=D9)),"ENV4",
IF(AND((G2<=G10)*(B2<=B10-0.25)*(C2<=C10-0.25)*(D2<=D10)),"#30",
IF(AND((G2<=G11)*(B2<=B11-0.25)*(C2<=C11-0.25)*(D2<=D11)),"#04",
IF(AND((G2<=G12)*(B2<=B12-0.25)*(C2<=C12-0.25)*(D2<=D12)),"#02",
IF(AND((G2<=G13)*(B2<=B13-0.25)*(C2<=C13-0.25)*(D2<=D13)),"#49",
IF(AND((G2<=G14)*(B2<=B14-0.25)*(C2<=C14-0.25)*(D2<=D14)),"#46",
IF(AND((G2<=G15)*(B2<=B15-0.25)*(C2<=C15-0.25)*(D2<=D15)),"#10_1",
IF(AND((G2<=G16)*(B2<=B16-0.25)*(C2<=C16-0.25)*(D2<=D16)),"#09",
IF(AND((G2<=G17)*(B2<=B17-0.25)*(C2<=C17-0.25)*(D2<=D17)),"#10",
IF(AND((G2<=G18)*(B2<=B18-0.25)*(C2<=C18-0.25)*(D2<=D18)),"#43",
IF(AND((G2<=G19)*(B2<=B19-0.25)*(C2<=C19-0.25)*(D2<=D19)),"#06",
IF(AND((G2<=G20)*(B2<=B20-0.25)*(C2<=C20-0.25)*(D2<=D20)),"#45",
IF(AND((G2<=G21)*(B2<=B21-0.25)*(C2<=C21-0.25)*(D2<=D21)),"#03_1",
IF(AND((G2<=G22)*(B2<=B22-0.25)*(C3<=C22-0.25)*(D2<=D22)),"#13_1",
IF(AND((G2<=G23)*(B2<=B23-0.25)*(C2<=C23-0.25)*(D2<=D23)),"#03",
IF(AND((G2<=G24)*(B2<=B24-0.25)*(C2<=C24-0.25)*(D2<=D24)),"#48",
IF(AND((G2<=G25)*(B2<=B25-0.25)*(C2<=C25-0.25)*(D2<=D25)),"#42",
IF(AND((G2<=G26)*(B2<=B26-0.25)*(C3<=C26-0.25)*(D2<=D26)),"#13",
IF(AND((G2<=G27)*(B2<=B27-0.25)*(C2<=C27-0.25)*(D2<=D27)),"#05",
IF(AND((G2<=G28)*(B2<=B28-0.25)*(C2<=C28-0.25)*(D2<=D28)),"#08",
IF(AND((G2<=G29)*(B2<=B29-0.25)*(C2<=C29-0.25)*(D2<=D29)),"#07_1",
IF(AND((G2<=G30)*(B2<=B30-0.25)*(C2<=C30-0.25)*(D2<=D30)),"#41",
IF(AND((G2<=G31)*(B2<=B31-0.25)*(C2<=C31-0.25)*(D2<=D31)),"#33_1",
IF(AND((G2<=G32)*(B2<=B32-0.25)*(C2<=C32-0.25)*(D2<=D32)),"#22",
IF(AND((G2<=G33)*(B2<=B33-0.25)*(C2<=C33-0.25)*(D2<=D33)),"#26_1",
IF(AND((G2<=G34)*(B2<=B34-0.25)*(C2<=C34-0.25)*(D2<=D34)),"#32_1",
IF(AND((G2<=G35)*(B2<=B35-0.25)*(C2<=C35-0.25)*(D2<=D35)),"#25_1",
IF(AND((G2<=G36)*(B2<=B36-0.25)*(C2<=C36-0.25)*(D2<=D36)),"#14_2",
IF(AND((G2<=G37)*(B2<=B37-0.25)*(C2<=C37-0.25)*(D2<=D37)),"#07",
IF(AND((G2<=G38)*(B2<=B38-0.25)*(C2<=C38-0.25)*(D2<=D38)),"#24_1",
IF(AND((G2<=G39)*(B2<=B39-0.25)*(C2<=C39-0.25)*(D2<=D39)),"#32",
IF(AND((G2<=G40)*(B2<=B40-0.25)*(C2<=C40-0.25)*(D2<=D40)),"#33",
IF(AND((G2<=G41)*(B2<=B41-0.25)*(C2<=C41-0.25)*(D2<=D41)),"#21_1",
IF(AND((G2<=G42)*(B2<=B42-0.25)*(C2<=C42-0.25)*(D2<=D42)),"#31",
IF(AND((G2<=G43)*(B2<=B43-0.25)*(C2<=C43-0.25)*(D2<=D43)),"#20_1",
IF(AND((G2<=G44)*(B2<=B44-0.25)*(C2<=C44-0.25)*(D2<=D44)),"#26",
IF(AND((G2<=G45)*(B2<=B45-0.25)*(C2<=C45-0.25)*(D2<=D45)),"#24",
IF(AND((G2<=G46)*(B2<=B46-0.25)*(C2<=C46-0.25)*(D2<=D46)),"#25",
IF(AND((G2<=G47)*(B2<=B47-0.25)*(C2<=C47-0.25)*(D2<=D47)),"#21",
IF(AND((G2<=G48)*(B2<=B48-0.25)*(C2<=C48-0.25)*(D2<=D48)),"#23_2",
IF(AND((G2<=G49)*(B2<=B49-0.25)*(C2<=C49-0.25)*(D2<=D49)),"#14_1",
IF(AND((G2<=G50)*(B2<=B50-0.25)*(C2<=C50-0.25)*(D2<=D50)),"#20",
IF(AND((G2<=G51)*(B2<=B51-0.25)*(C2<=C51-0.25)*(D2<=D51)),"#15_1",
IF(AND((G2<=G52)*(B2<=B52-0.25)*(C2<=C52-0.25)*(D2<=D52)),"#23_1",
IF(AND((G2<=G53)*(B2<=B53-0.25)*(C2<=C53-0.25)*(D2<=D53)),"#14",
IF(AND((G2<=G54)*(B2<=B54-0.25)*(C2<=C54-0.25)*(D2<=D54)),"#23",
IF(AND((G2<=G55)*(B2<=B55-0.25)*(C2<=C55-0.25)*(D2<=D55)),"#15",
IF(AND((G2<=G56)*(B2<=B56-0.25)*(C2<=C56-0.25)*(D2<=D56)),"#51X_2",
IF(AND((G2<=G57)*(B2<=B57-0.25)*(C2<=C57-0.25)*(D2<=D57)),"#51X_1",
IF(AND((G2<=G58)*(B2<=B58-0.25)*(C2<=C58-0.25)*(D2<=D58)),"#51X","OS3"
))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Images to help:
Thank you.
I just keep editing the current formula but it is becoming more and more cumbersome to keep up and change. Simply seeking help on how I can fix this and have no idea where to begin.
=IF(COUNTBLANK(B2:D2)>0,"Pending",
IFERROR(INDEX(Table1[Container Type],MATCH(1,(G2<=Table1[Volume])
*(B2<=(Table1[Full Length]-0.25))*(C2<=(Table1[Full Width]-0.25))
*(D2<=(Table1[Full Height])),0)),"OS3"))