Nested Formulas

EAllen · 03/21/19

I am attempting to use the nesting formulas to auto generate text showing the status of a project step that is co-dependent on several previous steps, but only when the previous steps have been selected.

For instance,we have an "Assigned" column which is just a check box, a column which indicates if that task is complete "Initial", and a Column that I want to auto generate the status of that task "Assignee".

What I want to happen is if row 62 is selected as being affected (ie. check box has been checked), and row 60 and 61 has been selected as affected and completed (initialed) then row 62 will auto generate "assigned" comment.

But I also want to allow for only row 60 or 61 to be selected as affected independently, as well as generating a "Pending" Status" Comment if either row 60 or 61 has been selected but not completed.

In addition I also want to classifying any combination affected rows and completed/not completed options to auto generate either "Pending", "Assigned", or "Completed" in the Assignee Column when we run a report. I was attempting to use the following formula to accommodate this, but it keeps saying it is unparseable.

=IF(AND(AFFECTED@ROW=1,INITIAL62=”EA”,AFFECTED60=1,INITIAL60=”EA”,AFFECTED61=1,INITIAL61=”EA”),”COMPLETED1”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=”EA”,AFFECTED61=1,INITIAL61=”EA”),”Assigned”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=””,AFFECTED61=1,INITIAL61=”EA”),”Pending1”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=”EA”,AFFECTED61=1,INITIAL61=””),”Pending2”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=””,AFFECTED61=1,INITIAL61=””),”Pending3”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=”EA”,AFFECTED61=0),”Assigned2”,IF(AND(AFFECTED@ROW=1,INITIAL62=”EA”,AFFECTED60=1,INITIAL60=”EA”,AFFECTED61=0),”COMPLETE2”,IF(AND(AFFECTED@ROW=1,INITIAL62=”EA”,AFFECTED61=1,INITIAL60=”EA”,AFFECTED60=0),”COMPLETE3”,IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60=””,AFFECTED61=0),”Pending4”,IF(AND(AFFECTED@ROW=1,AFFECTED61=1,INITIAL61=”EA”,AFFECTED60=0),”Assigned3”,IF(AND(AFFECTED@ROW=1,AFFECTED61=1,INITIAL61=””,AFFECTED60=0),”Pending5”,IF(AND(AFFECTED@ROW=1,AFFECTED61=0,AFFECTED60=0),”Assigned4”,IF(AND(AFFECTED@ROW=1,INITIAL62=”EA”,AFFECTED61=0,AFFECTED60=0),”COMPLETE4”,IF(AND(AFFECTED@ROW=1,AFFECTED61=0,AFFECED60=0),"COMPLETED"

Would any one have any suggestions or alternative options that may work better?

Paul Newcome · 03/22/19

Are you able to provide some screenshots? I am having trouble following what you are trying to do.

Eric M Oliveira · 03/25/19

Hello,

Happy to help! The UNPARSEABLE error appears when the formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

To correct this ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

For example, I would recommend removing all quotes and re-adding them. On my screen the quotes within the provided formula do not appear as open and closed quotes (""), instead, they appear as two closed quotes (””).

I would also suggest breaking out the formula to ensure each piece of the Nested IF Formula works. For example, in row 1 have the piece

=IF(AND(AFFECTED@ROW=1,INITIAL62="EA",AFFECTED60=1,INITIAL60="EA",AFFECTED61=1,INITIAL61="EA"),"COMPLETED1"

in row 2 have the piece

IF(AND(AFFECTED@ROW=1,AFFECTED60=1,INITIAL60="EA”,AFFECTED61=1,INITIAL61="EA”),"Assigned”

etc.

This will ensure each piece is working. Also, ensure each value has a closed Parenthesis at the end of each statement.

If the issue continues I would recommend connecting with our support team here: https://help.smartsheet.com/contact

That way we can set aside some time to have a screen share session.

Have a wonderful day,

Eric - Smartsheet Technical Support

Nested Formulas

Comments

Help Article Resources

Categories