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"))
👨🏼💻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.
 🆘 Smartsheet Consulting Inquiries:schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos:schoolofsheets.com/youtube
PS  If you have a follow up response attention use@Dan Palencharso I get notified of your reply!

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
Check out the Formula Handbook template!