Multiple IF ISBLANK NOT formula
What I'm trying to accomplish:
- If [Approved] is checked, show [Status] as "Complete"
- If [Ad RSS Link], [Subject Line], and [Done] are not blank, show [Status] as "Proofing"
- If [Ads Entered] is not blank, show [Status] as "Proofing"
- If all of the above are not true, show [Status] as "Waiting for Information"
Here is the formula I have, but it comes back as UNPARSEABLE. Any ideas or something I'm missing perhaps?
=IF(NOT(ISBLANK([Approved@row])), "Complete", IF(AND(NOT(ISBLANK([Ad RSS Link]@row)), NOT(ISBLANK([Done]@row)), NOT(ISBLANK([Subject Line]@row)), "Proofing", IF(NOT(ISBLANK([Ads Entered]@row)), "Proofing", "Waiting for Information"))))
Best Answer
-
When you reference a column in a formula, you only need [these] if the name either has a space or numbers in it. This means that your "Approved" column can be identified like this:
Approved@row not [Approved @row]
See: Create a Cell or Column Reference in a Formula
Then you can identify a checked box with the number 1 (0 means un-checked).
1 . If [Approved] is checked, show [Status] as "Complete"
=IF(Approved@row = 1, "Complete",
Then you can add both of your "Proofing" instructions into one IF statement. <> this means "is not equal to" and "" means "blank", so we can use <> "" to say "not blank".
2. If [Ad RSS Link], [Subject Line], and [Done] are not blank, show [Status] as "Proofing"
3. If [Ads Entered] is not blank, show [Status] as "Proofing"
IF(OR(AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> ""), [Ads Entered]@row <> ""), "Proofing",
Then, since you've already said all your other statements, you can add "Waiting for information" to the end.
FULL FORMULA:
=IF(Approved@row = 1, "Complete", IF(OR(AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> ""), [Ads Entered]@row <> ""), "Proofing", "Waiting for Information"))
Keep in mind if your "Done" column is also a checkbox, you may want to say
Done@row = 1 instead of Done@row <> ""
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
When you reference a column in a formula, you only need [these] if the name either has a space or numbers in it. This means that your "Approved" column can be identified like this:
Approved@row not [Approved @row]
See: Create a Cell or Column Reference in a Formula
Then you can identify a checked box with the number 1 (0 means un-checked).
1 . If [Approved] is checked, show [Status] as "Complete"
=IF(Approved@row = 1, "Complete",
Then you can add both of your "Proofing" instructions into one IF statement. <> this means "is not equal to" and "" means "blank", so we can use <> "" to say "not blank".
2. If [Ad RSS Link], [Subject Line], and [Done] are not blank, show [Status] as "Proofing"
3. If [Ads Entered] is not blank, show [Status] as "Proofing"
IF(OR(AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> ""), [Ads Entered]@row <> ""), "Proofing",
Then, since you've already said all your other statements, you can add "Waiting for information" to the end.
FULL FORMULA:
=IF(Approved@row = 1, "Complete", IF(OR(AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> ""), [Ads Entered]@row <> ""), "Proofing", "Waiting for Information"))
Keep in mind if your "Done" column is also a checkbox, you may want to say
Done@row = 1 instead of Done@row <> ""
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!