Help w/multiple if statements

lm56

Hello! I am a new Smartsheet user trying to figure out if multiple if statements work with what I'm trying to do. I would like to automate the date in the Release to SGS/Printer column based on what is in the Materials in House column and Printer column. The Materials in House column has pre-populated dates I manually enter. The printer column is set up as a dropdown multi-select. Each option in the printer column has a different variable i.e. subtract 84 days from Materials in House date if Printer is "AFS/Fuji" or substract 77 days from Materials in House date if Printer is Amcor. I've figured out how to have a singular if statement but when I combine them I get invalid or unparseable. I have 2 questions: (1) how can I combine the below if statements and (2) is there a better formula that will work with 23 if statements?

=IF(Printer12 = “AFS/Fuji”, [Materials in House]12 - 84)

=IF(Printer12 = "Amcor", [Materials in House]12 - 77)

=IF(Printer12 = “Atlantic”, [Materials in House]12 - 56)

=IF(Printer12 = “Belmark”, [Materials in House]12 - 42)

=IF(Printer12 = “CCL Mexico City”, [Materials in House]12 - 56)

=IF(Printer12 = “CCL Sioux Falls”, [Materials in House]12 - 84)

=IF(Printer12 = “Elopack”, [Materials in House]12 - 56)

=IF(Printer12 = “Evergreen”, [Materials in House]12 - 67)

=IF(Printer12 = “GPI”, [Materials in House]12 - 63)

=IF(Printer12 = “Green Bay (litho)”, [Materials in House]12 - 49)

=IF(Printer12 = “Green Bay (pre-print)”, [Materials in House]12 - 77)

=IF(Printer12 = “Green Bay (shipper)”, [Materials in House]12 - 49)

=IF(Printer12 = “HS Crocker”, [Materials in House]12 - 63)

=IF(Printer12 = “Huhtamaki”, [Materials in House]12 - 63)

=IF(Printer12 = “Lux”, [Materials in House]12 - 49)

=IF(Printer12 = “PCA (litho)”, [Materials in House]12 - 70)

=IF(Printer12 = “PCA (pre-print)”, [Materials in House]12 - 70)

=IF(Printer12 = “PCA (shipper)”, [Materials in House]12 - 35)

=IF(Printer12 = "Tetra Edge", [Materials in House]12 - 49)

=IF(Printer12 = “Tetra Prisma”, [Materials in House]12 - 56)

=IF(Printer12 = “Tetra Slim”, [Materials in House]12 - 49)

=IF(Printer12 = "Tetra Top (New Items)", [Materials in House]12 - 84)

=IF(Printer12 = “Tetra Top (Updates)”, [Materials in House]12 - 98)

Genevieve P.

Hi @lm56

Yes, we can combine all these statements! It won't be a Nested IF statement because you want your formula to check through each of the possible options and subtract dates based on each individual selection. Therefore, I'd actually add together each statement with a + sign.

You'll want to start with a statement that says if the cell is blank or TBD, return blank. If not, then you can go through all these options.

Then you can list the cell with the date first, and then all the IF statements like so:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84)

The HAS statement will see if the cell on the left HAS the one selection along with others.

Then lets add on our next value:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77)

Then the next:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77) + IF(HAS(Printer@row, "Atlantic"), 56)

And so on for the full formula:

=IF(OR([Materials In House]@row = "", [Materials In House]@row = "TBD"), "", [Materials In House]@row - (IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77) + IF(HAS(Printer@row, "Atlantic"), 56) + IF(HAS(Printer@row, "Belmark"), 42) + IF(HAS(Printer@row, "CCL Mexico City"), 56) + IF(HAS(Printer@row, "CCL Sioux Falls"), 84) + IF(HAS(Printer@row, "Elopack"), 56) + IF(HAS(Printer@row, "Evergreen"), 67) + IF(HAS(Printer@row, "GPI"), 63) + IF(HAS(Printer@row, "Green Bay (litho)"), 49) + IF(HAS(Printer@row, "Green Bay (pre-print)"), 77) + IF(HAS(Printer@row, "Green Bay (shipper)"), 49) + IF(HAS(Printer@row, "HS Crocker"), 63) + IF(HAS(Printer@row, "Huhtamaki"), 63) + IF(HAS(Printer@row, "Lux"), 49) + IF(HAS(Printer@row, "PCA (litho)"), 70) + IF(HAS(Printer@row, "PCA (pre-print)"), 70) + IF(HAS(Printer@row, "PCA (shipper)"), 35) + IF(HAS(Printer@row, "Tetra Edge"), 49) + IF(HAS(Printer@row, "Tetra Prisma"), 56) + IF(HAS(Printer@row, "Tetra Slim"), 49) + IF(HAS(Printer@row, "Tetra Top (New Items)"), 84) + IF(HAS(Printer@row, "Tetra Top (Updates)"), 98)))

One other thing I noticed is that some of your quotes are coming through slanted, like so: ”

You'll want to ensure they look like this: "

Let me know if this works for you and makes sense!

Cheers,

Genevieve

Genevieve P.

Hi @lm56

Yes, we can combine all these statements! It won't be a Nested IF statement because you want your formula to check through each of the possible options and subtract dates based on each individual selection. Therefore, I'd actually add together each statement with a + sign.

You'll want to start with a statement that says if the cell is blank or TBD, return blank. If not, then you can go through all these options.

Then you can list the cell with the date first, and then all the IF statements like so:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84)

The HAS statement will see if the cell on the left HAS the one selection along with others.

Then lets add on our next value:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77)

Then the next:

[Materials in House]@row - IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77) + IF(HAS(Printer@row, "Atlantic"), 56)

And so on for the full formula:

=IF(OR([Materials In House]@row = "", [Materials In House]@row = "TBD"), "", [Materials In House]@row - (IF(HAS(Printer@row, "AFS/Fuji"), 84) + IF(HAS(Printer@row, "Amcor"), 77) + IF(HAS(Printer@row, "Atlantic"), 56) + IF(HAS(Printer@row, "Belmark"), 42) + IF(HAS(Printer@row, "CCL Mexico City"), 56) + IF(HAS(Printer@row, "CCL Sioux Falls"), 84) + IF(HAS(Printer@row, "Elopack"), 56) + IF(HAS(Printer@row, "Evergreen"), 67) + IF(HAS(Printer@row, "GPI"), 63) + IF(HAS(Printer@row, "Green Bay (litho)"), 49) + IF(HAS(Printer@row, "Green Bay (pre-print)"), 77) + IF(HAS(Printer@row, "Green Bay (shipper)"), 49) + IF(HAS(Printer@row, "HS Crocker"), 63) + IF(HAS(Printer@row, "Huhtamaki"), 63) + IF(HAS(Printer@row, "Lux"), 49) + IF(HAS(Printer@row, "PCA (litho)"), 70) + IF(HAS(Printer@row, "PCA (pre-print)"), 70) + IF(HAS(Printer@row, "PCA (shipper)"), 35) + IF(HAS(Printer@row, "Tetra Edge"), 49) + IF(HAS(Printer@row, "Tetra Prisma"), 56) + IF(HAS(Printer@row, "Tetra Slim"), 49) + IF(HAS(Printer@row, "Tetra Top (New Items)"), 84) + IF(HAS(Printer@row, "Tetra Top (Updates)"), 98)))

One other thing I noticed is that some of your quotes are coming through slanted, like so: ”

You'll want to ensure they look like this: "

Let me know if this works for you and makes sense!

Cheers,

Genevieve

lm56

Hi @Genevieve P. thank you so much! This worked!

Genevieve P.

Wonderful! I'm glad I could help. 🙂