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

Ligy Alakkattussery

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

Ligy Alakkattussery

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.

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"))

Ligy Alakkattussery

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.