IF and ISBLANK For Color Statuses
Hello All,
I'm running into issues with my IF and ISBLANK formulas that will determine the color statuses.
Here are my columns.
Column 1- "Phase 2", this is a date field.
Column 2- "Sell Date", this is a date field.
Column 3- "RAMP", this is a symbols field, with the colors Red, Yellow and Green.
Here's what I'm looking to accomplish:
Column 3 should reflect Red if both "Column 1 & 2" are blank.
Column 3 should reflect Yellow if "Column 1" has a date and "Column 2" is blank
Column 3 should reflect Green if both "Column 1 & 2" have dates
Here is my current formula which gets my half way but not all the way.
=IF(ISBLANK([Phase 2]1), "Red", "Green")
I've tried to add the following to prompt the yellow but have been unsuccessful.
=IF(ISBLANK([Phase 2]1), "Red", =If(ISBLANIF(NOT(ISBLANK([Phase 2]1) AND IF(ISBLANK([SELL DATE]1), "Yellow"
Any help would be greatly appreciated as I've exhausted all my brain cells trying to get this to work.
THANK YOU!
Best Answers
-
The AND forumula is a unique entitity and requires a slight rewrite to your formula.
The And formula requires the grouping of two conditions within brackets, separated by comments.
Example:
AND(ISBLANK([Sell Date]1), Isblank([Phase 2]1))
So as you write out your formula, it would be something like this... IF(ISblank([Phase 2]1), "Red", IF(AND(ISBLANK([Sell Date]1), Isblank([Phase 2]1)), "Yellow", "Green"
You have to carefully open and close the and statement to make it work properly but this example should get you started.
-
There is one slight mistype that I see. You closed the isblank formula but you never closed the NOT formula here is how it should look. Try this, but if this doesn't work then you can try deleting the last closed parenthesis and pasting it in.
=IF(ISBLANK([Phase 2]1), "Red", IF(AND(NOT(ISBLANK([Phase 2]1)), ISBLANK([Sell Date]1)), "Yellow", "Green"))
Answers
-
The AND forumula is a unique entitity and requires a slight rewrite to your formula.
The And formula requires the grouping of two conditions within brackets, separated by comments.
Example:
AND(ISBLANK([Sell Date]1), Isblank([Phase 2]1))
So as you write out your formula, it would be something like this... IF(ISblank([Phase 2]1), "Red", IF(AND(ISBLANK([Sell Date]1), Isblank([Phase 2]1)), "Yellow", "Green"
You have to carefully open and close the and statement to make it work properly but this example should get you started.
-
Thank you, @Mike Wilday for the prompt response. I appreciate the insight. I've taken the foundation you've provided and tried to build off of it.
The wild card that I cannot figure out is the NOT portion of the formula. Here's what I have...
=IF(ISBLANK([Phase 2]1), "Red", IF(AND(NOT(ISBLANK([Phase 2]1), ISBLANK([Sell Date]1)), "Yellow", "Green")))
Correct me if I'm wrong but in order to generate the yellow status I need to account for 2 items. (1) [Phase 2] not being blank. (2) [Sell Date] being blank. Right?
-
There is one slight mistype that I see. You closed the isblank formula but you never closed the NOT formula here is how it should look. Try this, but if this doesn't work then you can try deleting the last closed parenthesis and pasting it in.
=IF(ISBLANK([Phase 2]1), "Red", IF(AND(NOT(ISBLANK([Phase 2]1)), ISBLANK([Sell Date]1)), "Yellow", "Green"))
-
Otherwise you had it right.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!