If Status is "Completed" then make % Complete "100%"

pentatonic145

I need help with the formula to automatically make the % Complete be 100% for any row where Status is "Completed"

Do I put the formula in the % Complete cell? And if so, what formula? What I have tried so far does not work:

=IF(Status=Completed,%Complete=100%)

That's probably wrong for a variety of reasons, but I can't find the answer in my searches...

Thanks!

Find more posts tagged with

Formulas

Comments

Jamison

For future reference - this page is super helpful: http://help.smartsheet.com/articles/775363-using-formulas

To answer your question:

Yes, put it in the % Complete Cell.

Depending on if you want to use the % Complete value as a number (in order to do comparisons like <, > ), you can do it a couple ways.

To be able to use the % Complete column as a number and have it display as a percent:

1. Change the column to % format: Highlight the % Complete column and select the % icon in the left hand menu.
2. Put =If(Status1="Complete", 1) in the first row of the % complete column. this will return 1 which will be formatted to 100%.

If you don't care that it is a number:

1. Put =If(Status1="Complete", "100%") - notice the 100% ias now in quotes - but you won't be able to do any comparisons or calculations with this value without additional steps.

The formula you have is missing the row number, parenetheses around your string (Complete), and since it is already in that column, you do not need to put "%complete=" in there, it already knows that.

pentatonic145

Huh. Thanks for the reply. It's not working for me though - I can't get the cell to show that a formula is in there (like, I don't see the prepended "fX" and it doesn't produce any result, ie "1" or "100%")

I just see literally what I type into the cell. It's not treating it like a formula, it's just treating it like plain text. The current type is "Text/Number"

I tried changing what you typed above to "Completed", since that's the value in the cell and I tried adding brackets around the value since I saw that in one of Smartsheet's examples (below) but it's still not calculating anything...

EXAMPLE:

=IF([Value 1]34 <= [Value 2]34, "Value 1 is less than or equal to Value 2", "Value 1 is greater than Value 2")

pentatonic145

OK to troubleshoot further, I created a new column called "TEST" and left the current type as "Text/Number" and now it does work, in that column.

I think part of the reason I can't make it work in the "% Complete" column (which is a column that existed by default in the Smartsheet template I used) is because the "column is being used in schedule calculations".

Jamison

Interesting - do you have a link to the template you used?

Edit - also, yes, you need to make sure the column name matches up with the formula. I may not have used the exact column name you did in my example.

Edit> I can't make it so anything that I start with a "=" doesn't automatically evaluate as a formula. Even just a lone "=" gives an "UNPARSEABLE" error.

Kennedy Stomps

Hi-- To confirm, we don't currently support using formulas in dependency-related columns when dependencies are enabled on a sheet. To get around this, you could either create the formulas in a second sheet, then use cell-linking to link them into your primary sheet or create the formulas in a new column in your primary sheet, link the values out to a secondary sheet, then cell-link them back to your primary sheet.

Jamison-- If you want to "write out" a formula in Smartsheet and not have it evaluate, you can preface the formula with an apostrophe ( ' )!

Pat Lockwood

Kennedy - that looks like it is working, but that was a bit painful. I chose the option of creating the formula on the main sheet, linking it out to a secondary and then linking it back to my %-Complete column.

Having an option to be able to create formulas in the dependency columns - at least not in the rows that are roll-ups from other rows, would be a great feature.

Thanks

Dornah Maryline

what if i have more than one option e.g. completed = 100% and not done=0%, how do i use the formula to pick either of the two percentages?

J. Craig Williams

Domah,

You'd likely build a Nested If (a series of IF statements) or combinations with the OR function.

Craig

JenValdez

Hi,

I tried doing the nested formula for the other options ("Not Started" equaling 0% and "In Progress" equaling 50%) but am not having any luck.

I'm not to savvy when it comes to formulas but this is what I had based on the first part of the formula:

=IF(OR(Status3="Complete", 1, IF(Status3=“In Progress”, .5, IF(Status3=“Not Started”, 0))))

Any help is much appreciated!

Jen

dbdb

I need to add on to this. I am absolutely shocked smartsheet is incapable of this. Using formulas in cells is basic spreadsheet 101.

Smartsheet absolutely needs to support formulas in dependency-related columns. Without this, nothing more than manually entered data is possible. And no, linking to other sheets, Zapier, or other 3rd party integrations are not acceptable solutions. Those options are useful if you're building a data pipeline between different data sources. We just need the ability to update cells in the same sheet based on other cells.

For the smartsheet product managers, here's my request:

1) Add support for formulas in dependency columns.

2) If formulas in dependency columns is a technical problem and simply too hard to solve, add triggers for setting cell content. Triggers already exist for conditional formatting. Just extend that functionality to set cell values instead of formatting. For example, when the "Status" column is changed to "Complete", it sets the "% Complete" column to 1 (100%). That would also solve this problem. See attached image mockup of what I would expect this to look like.

Mikey

Add my vote for this - I'm not too fussed about being able to write formula into dependency columns, I'd prefer it if the symbols that are designed to indicate progress (like the 0%, 25%, 50%, 75%, 100% 'Progress Pie' indicator) had the capability to be directly linked to % complete.

Am I the only one that thinks it's a bit weird that I can indicate (say) 25% progress on an activity by selecting a progress indicating symbol, but it is not linked to the tasks actual progress?

I've done the workaround thing of linking and updating from another sheet, but it's hardly an elegant solution, and needs careful management especially if lines are added or deleted in the target sheet.