# Need help getting comment to change in one box as each line status goes to complete.

Options
edited 12/09/19

This is my formula and it works only up to the line 2.  Starting with line 3, it will only work if the previous line(s) get reset to 0%.  I need the [Comments]1 to update as each [%Complete] status =1 with the next row Task and Assigned To.

=IF([% Complete]1 = 0, [Task Name]2 + ", " + [Assigned To]2,

IF([% Complete]2 = 1, [Task Name]3 + ", " + [Assigned To]3,

IF([% Complete]3 = 1, [Task Name]4 + ", " + [Assigned To]4,

IF([% Complete]4 = 1, [Task Name]5 + ", " + [Assigned To]5,

IF([% Complete]5 = 1, [Task Name]6 + ", " + [Assigned To]6,

IF([% Complete]6 = 1, [Task Name]7 + ", " + [Assigned To]7,

IF([% Complete]7 = 1, [Task Name]8 + ", " + [Assigned To]8,

IF([% Complete]8 = 1, [Task Name]9 + ", " + [Assigned To]9,

IF([% Complete]9 = 1, [Task Name]10 + ", " + [Assigned To]10,

IF([% Complete]10 = 1, "Complete"))))))))))

• ✭✭✭✭✭✭
Options

Hi Eleanor,

When using multiple IFs, it will only look for the first rule that is true so in your case it must be backward.

Try this.

=IF([% Complete]10 = 1; "Complete"; IF([% Complete]9 = 1; [Task Name]10 + "; " + [Assigned To]10; IF([% Complete]8 = 1; [Task Name]9 + "; " + [Assigned To]9; IF([% Complete]7 = 1; [Task Name]8 + "; " + [Assigned To]8; IF([% Complete]6 = 1; [Task Name]7 + "; " + [Assigned To]7; IF([% Complete]5 = 1; [Task Name]6 + "; " + [Assigned To]6; IF([% Complete]4 = 1; [Task Name]5 + "; " + [Assigned To]5; IF([% Complete]3 = 1; [Task Name]4 + "; " + [Assigned To]4; IF([% Complete]2 = 1; [Task Name]3 + "; " + [Assigned To]3; IF([% Complete]1 = 1; [Task Name]2 + "; " + [Assigned To]2; "Complete"))))))))))

The same version but with the below changes for your and others convenience.

=IF([% Complete]10 = 1, "Complete", IF([% Complete]9 = 1, [Task Name]10 + ", " + [Assigned To]10, IF([% Complete]8 = 1, [Task Name]9 + ", " + [Assigned To]9, IF([% Complete]7 = 1, [Task Name]8 + ", " + [Assigned To]8, IF([% Complete]6 = 1, [Task Name]7 + ", " + [Assigned To]7, IF([% Complete]5 = 1, [Task Name]6 + ", " + [Assigned To]6, IF([% Complete]4 = 1, [Task Name]5 + ", " + [Assigned To]5, IF([% Complete]3 = 1, [Task Name]4 + ", " + [Assigned To]4, IF([% Complete]2 = 1, [Task Name]3 + ", " + [Assigned To]3, IF([% Complete]1 = 1, [Task Name]2 + ", " + [Assigned To]2, "Complete"))))))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

You made it look so easy.  It didn't even cross my mind to backtrack.  Thank you so very much!  That totally solved the problem.

• ✭✭✭✭✭✭
Options

Happy to help!

Like everything it's easy when you know how to do it

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.