If in a column shows 100%, I would like it to automatically update as "Complete"



If in a column shows 100% (this info is from another sheet), I would like it to automatically update as "Complete".

I tried the formula, =IF([% Complete]Status20 = 1, "Complete") but it is not working.

When I put the formula below, it works for "In Progress" and " Not Started" but I can't seem to get to work for "Completed

=IF([Total Completion]20 > "0%", "In Progress", IF([Total Completion]20 = "100%", "Completed", "Not Started"))


Best Answer


  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hi Ligy,

    I made a video answering this question, you can view it at https://youtu.be/BK2LlFOLE6M.

    I believe your problem is the quotes around your "x%", this is causing the formula to interpret it as text. You should just be using numbers in the formula. If you're referencing data on the same sheet, you can use

    =IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))

    If you are referencing % Complete for the same job on a different Sheet, you can use nest an INDEX(MATCH()) formula within the Status formula to get the same results, where {% Complete} is the % Complete column on the separate sheet and {Job Info} is a column that is the same in both Sheets used for lookup purposes to identify the row in question.

    =IF(INDEX({%Complete}, MATCH([Job Info]@row, {JobInfo})) = 1, "Complete", IF(INDEX({%Complete}, MATCH([Job Info]@row, {JobInfo})) > 0, "In Progress", "Not Started"))

    👨🏼💻Dan Palenchar |School of Sheets Solutions Consulting| Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response attention use@Dan Palencharso I get notified of your reply!

  • Ligy Alakkattussery
    Answer ✓

    Thanks Dan!

    I watched your video. Very well explained and clear.

    Bonus for explaining the Index Match functionality, I am not there yet but looking forward to using it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!