Nested Formulas

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?

Find more posts tagged with

Manufacturing

Healthcare

Formulas

Reports

Support

Product Development

Conditional Formatting

Project Management

Comments

Paul Newcome

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

Eric M Oliveira

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…