Nested If/Then Quantry
Answers
-
Would this work?
=IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(Status@row = "Not complete", AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "True", "False")
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
@Jason Albrecht Thank you but I am still getting the "UNPARSEABLE" error. I tried to troubleshoot and I changed the status from being "Not Complete" to NOT(status="Complete") to no avail:
=IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(NOT(Status@row = "Complete"), AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "True", "False")
-
#UNPARSEABLE happens for many reasons, such as misspelling, wrong case for a column name, etc.
I would break the formula down into each component and check that it works.
e.g. does [Primary Lead]@row = [Modified By]@row work? If not, I'd fix it, try the full formula, and if still getting an error, move on to the next section (e.g. does Type@row = "Action Plan" work, etc.)
As we don't have a screenshot of your column names to check, it could be that some of these should be in uppercase.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Found the issue was with the due date data entered by the user was missing. I added an iferror to capture that.
The only part that is not working is the last criteria for "[Primary Lead] = "Current User" (and also [Secondary Lead] = "Current User")
I see that the first part compares the Primary Lead to the last user who Modified the row "[Primary Lead]@row = [Modified By]@row" is there a way to have this filter apply to the "Current User" in either the [Primary Lead] or the [Secondary Lead] column?
I tried using a filter in grid view with the settings of "Show rows that match All conditions" it fails as the Primary will not be the Secondary.
"Primary Lead' "Is One Of" "Current User"
"Secondary Lead" "Has Any of" "Current User"
"Active" "Is One Of" "Yes, Hold"
The "Active" column =IFERROR(IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(NOT(Status@row = "Complete"), AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "Yes", "No"), "Hold")
Thank you for helping.
-
I'm glad we've got this far and that you found the issue with the due date data missing.
You'll need to help me understand what you mean by "Current User" though, so that the community can help out.
In reference to this article on column types, the only system column suitable that I can see is the [Modified By], where, once the person who is currently accessing the sheet saves the sheet, their details will be shown in the column.
Thanks in advance for your patience with me as I seek some understanding. Talk to us a bit more about "Current User" and provide some screenshots of the grid view filter to which you referred. Is this a separate column to Primary Lead and Secondary Lead? Could you create a report of the sheet automatically filtered for each lead and shared with each lead, either directly or via a dashboard? What happens if two Primary Leads are accessing the sheet at the same time? Looking forward to helping further, once I understand the context a bit better.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
So here are the fields we have been playing with. I realize that Smartsheet does not support "Current User" natively in a formula, only in a Report.
The report I tried had the settings of "Show rows that match All conditions":
"Primary Lead' "Is One Of" "Current User"
"Secondary Lead" "Has Any of" "Current User"
"Active" "Is One Of" "Yes, Hold"
However, to use a report I have to use only one boolean type at a time (all "OR" or all "AND") so that will make it hard to do as I need (Primary OR Secondary) AND Active
-
To close out this conversation, here is the formula we are using:
=IF(Status@row = "Canceled", "No", IF(ISBLANK([Due Date]@row), IF(AND(Status@row = "Complete", ISBLANK([Publish Date]@row)), "No", IF(AND(Status@row <> "Complete", ISBLANK([Publish Date]@row)), "Yes", IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(NETDAYS([Publish Date]@row, TODAY()) > 0, IF(Status@row = "Complete", "No", "Yes"), IF(NETDAYS(TODAY(), [Publish Date]@row) <= 90, "Yes", "No")), "No"))), IF(ISBLANK([Publish Date]@row), IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(NETDAYS([Due Date]@row, TODAY()) > 0, IF(Status@row = "Complete", "No", "Yes"), IF(NETDAYS(TODAY(), [Due Date]@row) <= 90, "Yes", "No")), "No"), IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(OR(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0), IF(Status@row = "Complete", "No", "Yes"), IF(OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), "Yes", "No")), "Hold"))))
this gives only items that are Action Plan or Key Task and not cancelled; then, if past the dates and not marked complete; or within the next 90 days
-
Thank you @thecomputermedic
Congratulations on completing a mammoth formula.
I'm curious about your quandary regarding Current users.
How did you go about solving that in the formula, or does the formula provide the data and the report filters for the appropriate user?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
I used the formula in a new column in a report I chose the fields that had a Yes in the new column and that is where I was able to set current user. I also have a filter in the sheet that is current user AND Yes.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 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!