Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
Ok. Here is what I would like to do. I would like the ability for a user to enter their % complete in a cell and once that reaches 100% the check box will auto check. I would also like them to be able to click the completed box and have the % complete cell fill in with 100%. How I want to accomplish this is by using a couple of hidden columns. I just haven't been able to find the formula that I would put in column A to update column B when something is entered in Column C.
I currently do this for my project plans. Here are the steps I have built in my sheets to do this.
1. Create columns: % Complete and Status (I use this instead of a check box)
2. % Complete is a text/number field (nothing special)
3. The Status field is a) drop down and b) contains the formula below
3a) Drop down contains In Progress, Complete (I use for conditional formula)
3b) Formula =IF([% Complete]1 = 1, "Complete", IF([% Complete]1 > 0, "In Progress", ""))
4. I then HIDE the "Status" column
5. Conditional Formula is created - used to highlight rows based on status
Status is 'Complete' - apply Green shading to row
Status is not 'Complete' and Finish is in the next 8 days apply orange shading to row (Flags those tasks coming due)
Status is not 'Complete' and Finish is in the past then apply Red shading to the row
Note: By default since no data has been input for %Complete the row is not highlighted
Functionality
1. Task owner is responsible for entering the %Complete as task is completed.
2. Entering any number greater than zero but less than 100% will change the 'Status' column to 'In Progress'
3. When task is marked as 100% the status is changed to 'Complete' and the row is shaded in Green
