Formula for a checkbox

11/29/21
Accepted

Ok so what I am trying to do is put a formula in the background of a checkbox.

The purpose is the have the formula check a name in a cell, and then compare that value with a list on another sheet. At the moment I have that list typed one name per cell vertically however I would prefer to be able to put the list into a dropdown list to negate having to change the reference range.

If the formula finds a match then I want it to leave the checkbox unchecked, however if no match is found in the list then I want it to tick the checkbox.

If I can put the list in a drop down the I would prefer the formula to make the dropdown list reference absolute so that when the formula fills down it always references the same dropdown.

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    So you are trying to check a box on Sheet A if the name is not found on Sheet B? You shouldn't need dropdowns or anything.

    The vertical list on Sheet B should be fine. When you create your cross sheet reference, make sure you are clicking on the column header in Sheet B so that you reference the entire column.

    =IF(CONTAINS([Sheet A Name Column]@row, {Sheet B Name Column}), 0, 1)

    thinkspi.com

Answers

  • OK, a few things here.


    Natively, Smartsheet doesn't allow dropdowns to reference cells for the list. This is possible with a 3rd party app called DDUpdate which comes at a small cost. So if you need your dropdown to reference a live list this is the best option I know of.

    Checkbox syntax is 1 for checked and 0 for unchecked. So you could write a formula to automate checking of boxes... you haven't given enough info but generally the formula would be:

    =IF([whatever condition for checking the box],1,0)


    Hope that helps.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Josh Osborn

    I hope you're well and safe!

    To add to BullandKhmer's excellent advice/answer.

    Another Smartsheet option would be to use the Premium app, Data Shuttle to "sync" the dropdown lists.

    Is that an option?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    So you are trying to check a box on Sheet A if the name is not found on Sheet B? You shouldn't need dropdowns or anything.

    The vertical list on Sheet B should be fine. When you create your cross sheet reference, make sure you are clicking on the column header in Sheet B so that you reference the entire column.

    =IF(CONTAINS([Sheet A Name Column]@row, {Sheet B Name Column}), 0, 1)

    thinkspi.com

Sign In or Register to comment.