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

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!