Automatic Checkbox

Hello I am trying to get a checkbox to automatical put a checkmark in it when a certain status (done) is written but I cannot get it in the conditional formating. Also trying to get the rule the other way as well so that if checkbox is checked that status changes automatically to done but can't seem to find it in the condition. Any ideas?

 

 

smartsheet.jpg

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hello Consey Coffee, 

    You can add formulas to both columns to do the trick. In the Statut Column Row 32 add IF(Terminer32=1, "Done") 

    In the Terminer column row 32, add "IF(Statut32="done", 1) 

    Be warned however, if you have formulas in those columns, then they become uneditable in a report. Users can't edit fields that contain a formula. Also, if they overwrite the formula field you may have issues with it auto-filling to the next row. 

    You may want to consider user-workflow in this case and make sure that your users are either 1) editing rows on the main smartsheet or 2) limit what your users do, either write done, or click the checkbox.  Maybe also consider using a dropdown column on the statut row so that users can select a status... and keep the checkbox as a formula item.

    More on cell-linking and reports: https://community.smartsheet.com/discussion/cell-linkingformulas-reports

    More on auto-fill: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting... if the final row in your smartsheets contains a formula, new rows will also populate with that formula. but if you overwrite the formula with text auto-fill stops working. 

  • where do I put the formula exactly?

  • i am sorry I don't really understand how to get the formula in the collumn as I already have a given rolldown menu to choose from... or a checkbox to click on

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Double click on the rolldown menu, or on the checkbox to get a textfield and then paste the formula in there. Then you can enter the formula there. Users will be able to select the dropdown or enter the checkmark from the original sheet only. 

     

  • Consy Coffee
    edited 01/04/18

    I have tried I can't do it as it keeps erasing information in my scroll down menu and  it doesn't like that I ask one to do something ot the other and the oppiste, says blocked because of double reference or something like that.  unparseable 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    That makes sense, it becomes circular reference if one relies on the other. Unless someone else has another solution, I think you're going to have to consider the workflow for this piece. Either use one method or the other. Have users click a finished checkbox and have the status write Done. OR, have users select the status via a dropdown and have the checkbox check off automatically. 

    You might be able to do what you want by integrating a third party solution called Zapier but I don't have much practice in that. J. Craig who can be found on the All-time tab on the scoreboard can help you with that. His contact information is in his profile. 

  • Robert S.
    Robert S. Employee

    Hello,

     

    Thanks for the question. Mike's latest response is correct. Formulas that reference each other will cause a circular reference and are illogical statements, so they aren't possible. To make this possible, you can have it go one way or the other, but not both.

     

    Another reason for this, is if a cell contains a formula and is then manually changed the formula is deleted from the cell. This is the case for any update to the cell, such as checking a box or changing the text shown.

     

    To become more familiar with writing formulas in Smartsheet, take a look at our help center on this (https://help.smartsheet.com/articles/2476171). 

  • Robert S.
    Robert S. Employee

    Hello,

     

    Thanks for the question. Mike's latest response is correct. Formulas that reference each other will cause a circular reference and are illogical statements, so they aren't possible. To make this possible, you can have it go one way or the other, but not both.

     

    Another reason for this, is if a cell contains a formula and is then manually changed the formula is deleted from the cell. This is the case for any update to the cell, such as checking a box or changing the text shown.

     

    To become more familiar with writing formulas in Smartsheet, take a look at our help center on this (https://help.smartsheet.com/articles/2476171). 

  • davidnguyen
    davidnguyen Employee Admin

    thanks for problem link to HELP page Robert. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!