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
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!