Nested IF, OR, AND with TODAY formula to return a symbol (RYGB) based on "Drop Down Menu Status"
Hi All,
I'm trying to set a symbol (Red, Yellow, Green, Blue) in a column based on the individual values in one column.
Here is what I have so far (but it is wrong):
=IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND(TODAY()[Current Status]@row = "Current pending", [Review Due Date]@row > 1, "Red", IF(OR([Current Status]@row = "Approved", "Closed", "Blue"))))))
Please help me!
-Aris
Answers
-
Hi @Aris
Looks like you are missing the condition in your Today section of your AND statement, closing parenthesis for your And statement. You should be able to add your condition and closing parenthesis, and delete one closing parenthesis and be set.
Current: IF(AND(TODAY()[Current Status]@row = "Current pending"
Hope this helps,
best,
Brad
www.MVPOPS.com
-
Hi MVP OPS,
Thank you it looks to have made the formula somewhat work. I am receiving another error now for "INCORRECT ARGUMENT SET".
Does this have to do with last part of my formula? Or the AND statement/TODAY argument logic?
Thanks,
-
Hi @Aris
if you can copy and paste your formula as it looks now I can help identify the issue.
Looking at your original formula the end is missing items as well, it should look like this:
IF(OR([Current Status]@row = "Approved",[Current Status]@row = "Closed"), "Blue"
best,
Brad
www.MVPOPS.com
-
=IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND([Current Status]@row = "Current pending", TODAY([Review Due Date]@row > 1, "Red", IF(OR([Current Status]@row = "Approved", [Current Status]@row = "Closed"), "Blue"))))))
-
Thank you try this one:
=IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND([Current Status]@row = "Current pending", TODAY([Review Due Date]@row) > 1), "Red", IF(OR([Current Status]@row = "Approved", [Current Status]@row = "Closed"), "Blue"
best,
Brad
www.MVPOPS.com
-
Hi Brad,
It has returned "#INVALID DATA TYPE" for those fields that previously stated "#INCORRECT ARGUMENT SET"
-
Hi Aris,
this could be due to the non date input in the review date field. try removing the "closed" from the date field.
best,
Brad
www.MVPOPS.com
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives