If in a column shows 100%, I would like it to automatically update as "Complete"
Hello,
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"))
Thanks
Best 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.
Answers

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"))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!