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

Alexis Kipping

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 :)

Paul Newcome

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?

Paul Newcome

Are you able to provide more detail and possibly some screenshots?

Alexis Kipping

The NPR and Gate 3-5 decisions can be Approved, On-Hold or Cancelled. They occur sequential NPR to Gate 3 to Gate 4 to Gate 5. I would like Current Stage (Auto) to reflect what the current stage is based on the approvals. If any of the Approvals = Cancelled or On-hold, then Current Stage (Auto) = n/a. If Project Type = NSG, then Current Stage (Auto) = NSG.

Alexis Kipping

Paul Newcome

Ok. So to make sure I understand...

If the Project Type = "NSG" then output "NSG".

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 both of the above are false, then output what exactly? The column name (NPR, Gate 3, Gate 4, or Gate 5)?

I notice you have hidden columns in between the decision columns. What kind of data can be in those columns? Is it possible that the hidden columns could contain the same text value as one of the decision columns?

Alexis Kipping

Thanks for the follow-up. I appreciate the help. What you said above is nearly correct. As much as i would like the hidden columns to be relevant to this, they are not and contain financial values.

If NPR Decision = Approved, and Gate 3-5 Decision are blank, then Current stage = Stage 2.

If Gate 3 Decision = Approved, and Gate 4-5 Decision are blank, then Current Stage = Stage 3

If Gate 4 Decision = Approved, and Gate 5 is Decision is blank, then Current Stage = Stage 4

If Gate 5 Decision = Approved, then Current Stage = Stage 5.

I believe a convoluted, if, and, or equation could also be used, but it was rather large in excel and this equation above was an easier way to make it work in excel, but it's not working in smartsheet.

Paul Newcome

The reason I asked about the hidden columns is because if we know those columns will NOT contain a text string that we are using in the Decision columns, then we can look across a range for specific text instead of having to specify each cell in the row.

ColumnA.....ColumnB.....ColumnC

Y.......................#...................N

So in the above, if ColumnB is ALWAYS going to be a number, then we can just look across the range of ColumnA:ColumnC for either a "Y" or an "N" and we know that ColumnB will never trigger a false result since it will always be a number.

Does that make sense? Granted this smaller scale doesn't make a huge difference, but on a larger scale with 4 columns to evaluate and numerous columns in between, if we know those "in between" columns will never contain "Approved", "On-Hold", or "Cancelled" then we can just use [NPR Decision]:[Gate 5 Decision] instead of having to specifically call out all four columns.

If we can do this range, it will greatly simplify everything, but it is still possible if we have to call out each individual column.

Alexis Kipping

Interesting idea. The data in the hidden columns is financials or dates, so we should be able to use the range. Could you help set up the rest of the formula?

Paul Newcome

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?

Alexis Kipping

That worked! Thank you so much. So I realized it needs one more condition and I'm not sure where to add it. If Gate 5 Decision = Approved AND 1st Shipment Date has any value, then Current Stage = PLR. I forgot about this recent addition. Any thoughts as to how to incorporate that? You have been so helpful already :)

Paul Newcome

I would suggest adding it after the "NSG" section like so:

=IF([Project Type]@row = "NSG", "NSG", IF(AND([Gate 5 Decision]@row = "Approved", [1st Shipment Date]@row <> ""), "PLR", 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))))

Alexis Kipping

I copied and pasted the updated formula, and I am not receiving this error. Thoughts? Thank you in advance.

Paul Newcome

Hmm... Try converting it back into a cell formula instead of a column formula and see what happens.

Alexis Kipping

Switching back to a cell formula yields #unparseable.

Paul Newcome

Is this the correct column name?

 [1st Shipment Date]

Alexis Kipping

Good catch, as it was 1st Available Ship Date, and I updated the formula to below, and it's still yielding #unparseable.

=IF([Project Type]@row = "NSG", "NSG", IF(AND([Gate 5 Decision]@row = "Approved", [1st Available Ship Date]@row@row <> ""), "PLR", 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))))