Nested IF formula breaking out too early
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
Answers
-
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.
-
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
-
That's exactly what I was thinking. Glad you were able to get it working.👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!