Status dropdown Vs % Complete calculation

Hi all, I'm hoping you can help me with a thorny problem.

I have a 'Status' column, which is a dropdown which contains 'Not Started'/ 'In Progress'/ 'Completed'. I was also using the '% Completed' column with dependencies to rollup the child rows and give a percentage total in the parent rows.

My users asked if '% Complete' could be automatically updated to '100%' if 'Complete' was selected in the 'Status' column, which I enabled by using this formula in '% Complete'

=IF(Status6 = "Complete", 1, 0)

All was going fine, until I realised that by doing this, I had disabled the parent row rollup feature. My parent rows are now all stuck at 0%. If I use 'Sum Children' then it just adds up all the 100%s in the child rows and displays a total of '800%'.

I think I need to reinstate system column of '% Complete', but then that would mean we're back to updating both 'Status' and then '100%' on every row. Can anyone help me with any bright ideas?

Many thanks, Lisa

Best Answers



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!