Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Method to allow more than on option in a cell
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.
Comments
-
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
