Method to allow more than on option in a cell

Jeff Smith

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. 

Bob Stevens

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