Nested IF formula breaking out too early

twarner

Hi!

I have a series of columns describing deployment dates. Users can enter up to 8 deployment dates (1 per column) and an associated Type (of deployment) column:

Production Event Date 1 = 2/15/22

Production Event Date 1 - ON / OFF / LIVE = OFF

Production Event Date 2 = 2/20/22

Production Event Date 2 - ON / OFF / LIVE = ON/LIVE

Production Event Date 3 = 3/1/22

Production Event Date 3 - ON / OFF / LIVE = OFF

Production Event Date 4 = 3/15/22

Production Event Date 4 - ON / OFF / LIVE =ON/LIVE

...

I'm using the following code to run through each of the column groupings to fill another column with a list of deployment dates. I ultimately want a column that has a list of dates that are "OFF" and a list of dates that are "ON" - formatted for reading such as:

OFF deployments: "2/15/22, 3/1/22"

LIVE deployments: "2/20/22, 3/15/22"

The below is for finding all the "OFF" deployments:

=IF(OR(ISBLANK([Production Event - Date 1]@row), [Production Event (Date 1) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", [Production Event - Date 1]@row + IF(OR(ISBLANK([Production Event - Date 2]@row), [Production Event (Date 2) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 2]@row + IF(OR(ISBLANK([Production Event - Date 3]@row), [Production Event (Date 3) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 3]@row + IF(OR(ISBLANK([Production Event - Date 4]@row), [Production Event (Date 4) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 4]@row + IF(OR(ISBLANK([Production Event - Date 5]@row), [Production Event (Date 5) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 5]@row + IF(OR(ISBLANK([Production Event - Date 6]@row), [Production Event (Date 6) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 6]@row + IF(OR(ISBLANK([Production Event - Date 7]@row), [Production Event (Date 7) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 7]@row + IF(OR(ISBLANK([Production Event - Date 8]@row), [Production Event (Date 8) - ON / OFF / LIVE]@row <> "ON/LIVE"), "", ", " + [Production Event - Date 8]@row))))))))

The problem is that the first time an IF statement finds a TRUE logical expression, the whole statement ends with "success". I need to be able to account for Dates that are not sequential and multiple OFF or ON/LIVE dates.

Is there a way to do this in SmartSheet? I would do this in a FOR loop in a normal programming language.

Thanks for your help!

Tony

Paul Newcome

Are you able to provide a screenshot for reference? Depending on your structure, we may be able to do this with a much simpler formula.

twarner

Paul - you are totally right! I was completely forgetting about the JOIN function! Here is what I came up with that does exactly what I was looking for:

=JOIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]@row:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]@row, <>"LIVE"), ", ")

Thanks for pushing me to think differently.

Tony

Paul Newcome

That's exactly what I was thinking. Glad you were able to get it working.👍️