I have a formula using IF, AND, ISERROR, and COUNT in excel (see below), which works. =IF(AND(ISERROR(FIND("Cancelled",AE2&AP2&AY2&BG2,1)),ISERROR(FIND("On-Hold",AE2&AP2&AY2&BG2,1)),K2<>"NSG"),"Stage "&COUNTA(AE2,AP2,AY2,BG2)+1,"n/a") I'm trying to update the formula to work in smartsheet, and have updated to this point, and can't figure out where I am going wrong. I keep on getting #UNPARSEABLE. I feel I'm missing a parenthesis or comma somewhere, but can't figure out where. This is the formula I have in smartsheet currently =IF(AND(ISERROR(FIND("Cancelled",[NPR Decision]@row&[Gate 3 Decision]@row&[Gate 4 Decision]@row&[Gate 5 Decision]@row,1))),(ISERROR(FIND("On-Hold",[NPR Decision]@row&[Gate 3 Decision]@row&[Gate 4 Decision]@row&[Gate 5 Decision]@row,1))),Project Type<>"NSG","Stage"&COUNT([NPR Decision]@row,[Gate 3 Decision]@row,[Gate 4 Decision]@row,[Gate 5 Decision]@row)+1,"n/a") I am also open to any other ideas/suggestions to achieve a similar end. Thank you in advance :)

Great! That really simplifies things a lot! So here is the logic: If the Project Type = "NSG" then output "NSG". If the above is not true and if one or more of the NPR, Gate 3, Gate 4, or Gate 5 Decision fields are "Cancelled" or "On-hold" then output "n/a". If the above is not true and if Gate 5 is "Approved" then output "Stage 5". If the above is not true and if Gate 4 is "Approved" then output "Stage 4". If the above is not true and if Gate 3 is "Approved" then output "Stage 3". If the above is not true and if NPR is "Approved" then output "Stage 2". And here is the formula: =IF([Project Type]@row = "NSG", "NSG", IF(OR(CONTAINS("Cancelled", [NPR Decision]@row:[Gate 5 Decision]@row), CONTAINS("On-hold", [NPR Decision]@row:[Gate 5 Decision]@row)), "n/a", "Stage " + (COUNTIFS([NPR Decision]@row:[Gate 5 Decision]@row, "Approved") + 1))) And to read the formula: If the Project Type = "NSG" then output "NSG". If the above is not true and if one or more of the NPR, Gate 3, Gate 4, or Gate 5 Decision fields are "Cancelled" or "On-hold" then output "n/a". If the above is not true then output "Stage " and the number of times "Approved" is found +1. How does that look?

Update project status column based on the information in multiple columns, using IF, AND, ISERROR

Answers

Paul Newcome ✭✭✭✭✭✭

01/05/21

It is because you accidentally doubled up on the "@row" when you updated the column name.

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

NEED TO ADD DAYS TO THIS FORMULA IF ANOTHER CELL HAS SPECIFIC ITEMS OF A DROP DOWN.
I have this formula that works for this cell when information is missing from reference cell A. However now we want to be able to add additional days if reference cell B has specific items checked in a dropdown. I just can't seem to get it to work. Any suggestions. =IF(ISBLANK([ ENGINEER (tentative start date)]@row), "",…
Check a Box if Between Dates on Another Sheet
Hey all, I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet. I've tried a million variations but here's what I have so far, thinking I was overcomplicating things =COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row,…
How to Automatically Checkmark a Column for entries that are created within a time-based timeframe
I'm trying to write a formula that will automatically place a checkmark in a checkbox column titled "Today Entry" whenever entries meet the "Created" column criteria of being submitted after 1100 AM the day prior and 0800 AM today on a rolling basis: I've been trying to use the below column, but honestly I have no idea. I…

Update project status column based on the information in multiple columns, using IF, AND, ISERROR

Answers

Help Article Resources

Categories