Multiple % Complete Columns?

Rob W.

Hi all,

Overview: I'm looking for a way to have more than 1 '% complete' column in a sheet

Application: I'm building a project compliance matrix which will have a row for each requirement, and multiple columns for associated tasks. I'm not using this as a schedule / GANTT, but have turned on dependencies as this allowed me to build a WBS structure and use parent / child relationships to automatically calculate %C at varying depths.

Problem: I need to monitor more than one task for each row, and have created %-formatted columns to suit (Performance / Documentation / Testing). Unfortunately, the project settings only allows selection of a single column to capture sheet % complete. I can understand why, but still want to make these columns calculate properly, even if there can only be one 'official' project % complete value.

Approach: I thought about setting up a formula to average %C of row children which would be fine for this application. Problem is this can't be a column formula since the column includes both task (manual) and rollup (formula) values, and I don't want to mix manual entry with formulas since this won't scale automatically as new rows are added or nested. Would be less worried if this wasn't a shared / collaborative document.

Feels like this is probably not possible on the front end, while being technically possible as evidenced by the existing project %C functionality. Still, figured I'd ask for any clever workarounds that might get me the output I'm after despite this not being a standard capability.

Thanks in advance!

Hollie Green

The only suggestion I have would require a few helper columns.

One to indicate if it would be a manual row or a formula row if you did want to mix them. It would require checking that box when a new row is added if the row will be a formula row.

Have one column for your manual entries

One column for the formula entries - this one could be hidden and be a column formula because the column that counts will only pull in the data if the box is checked for the formula

And one column that is locked that provides the data to roll up with the below formula that way if the box isn't checked it will pull in the manual row and if it shows up blank you will know to check to see if a manual entry is missing or if the box needs to be checked.

=If([helper column for manual/formula]@row=1,[formula entry column]@row,[manual entry column]@row)