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

Options

Answers

Paul Newcome Community Champion

01/05/21

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

CERTIFIED SMARTSHEET PLATINUM PARTNER
10x Time Savers (An exclusive group that grants you access our Smartsheet master classes, tips, tricks, & much more!)
Training opportunities with my team and I
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

Net Days showing 36500 no matter the actual dates provided.
I am trying to use the NETDAYS function, but it will only respond with 36500. The result should be like 60. The dates I am trying to count are part of some simple (COLUMN + COLUMN) formula.
Trying to return 3 months prior to Date Column
Hello, I am trying to make a column to return dates 3 month before the date in another column. Essentially going to be notifying teams 3 months before something expires. I have a column for the dates. It is set to "date" and i'm using this formula in a helper column =DATE(YEAR(DATE@row ), MONTH(DATE@row ) - 3, DAY(DATE@row…
Move data from a column into a cell
I would like to pull the name that is listed in the Name column into the Primary Column. Note that the name could be listed multiple times in the Name Column.

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

Answers

Help Article Resources

Categories