Check Box & IF Statements

BobLonelyBobLonely ✭✭✭✭
edited 12/09/19 in Formulas and Functions
12/04/18 Edited 12/09/19

Appreciate some pointers on this if possible..

 

I have 5 Columns in my Smartsheet.. the first 4 are Check Boxes, the 5th is a Text/Number.. Columns as follows..

 

[North]

[East]

[South]

[West]

[Direction]

 

In [Direction], I want to display text dependant on the Checked Boxes status.. it can be any of the first 4 boxes or any combination of the boxes..

 

Using this formula =IF([North]1 = 1, "North ", "") I can get the "North "to display, but cannot work out how to modify the formula to then check for the other Columns.. for example, I have [North] and [South] checked, I would expect a return of "North " and "South "..

 

Appreciate your support..

Regards..

BobLonely..

 

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    You would have to check for each possible combination. 

    =IF(And([email protected] = 1, [email protected]=1),"North South", IF(And([email protected]=1, [email protected] = 1, [email protected]=1, [email protected] =1), "North East South West", IF... 

    You would have to account for all four being selected, each possible combination of 3, of 2, and each of them separately. It will be a fairly detailed nested IF statement using AND to account for each selection. 

    https://help.smartsheet.com/function/and

    https://help.smartsheet.com/function/if

  • BobLonelyBobLonely ✭✭✭✭

    Wow.. one big Nested IF statement..

     

    Thanks..

  • Mike WildayMike Wilday ✭✭✭✭✭

    Yep! You're welcome! 

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 12/04/18

    Hi Simon,

    Another option to Mikes excellent suggestion with a nested IF statement could be to use helper columns and the JOIN in combination with the COLLECT function.

    In my example, I use NH for North (Helper), EH East (Helper) and so on.

    Edit: I forgot to add the formula for the helper columns. Change North for each direction.

    =IF([email protected] = 1; "North")    

    The same version but with the below changes for your and others convenience.    

    =IF([email protected] = 1, "North")

    The formula will only show the AND delimiter for the cells that are not empty.

    I.e., North AND East AND South AND West

    =JOIN(COLLECT([email protected]:[email protected]; [email protected]:[email protected]; NOT(ISBLANK(@cell))); " AND ")    

    The same version but with the below changes for your and others convenience.   

    =JOIN(COLLECT([email protected]:[email protected], [email protected]:[email protected], NOT(ISBLANK(@cell))), " AND ")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    I hope this helps you!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Mike WildayMike Wilday ✭✭✭✭✭

    Hi Andree, 

    I'm assuming you have 4 helper columns, and each helper column has a single formula to check if the north is checked? 

    Like this for the NH: =IF([email protected]= 1, "North", "") 

    And repeated for each of the helper columns and directions. Then you use the Join statement to send the results? 

     

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

    Hi Mike,

    Correct, but I forgot to put the formula in the description before. Now it's there.

    Best,

    Andrée

    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 ✭✭✭✭✭

    A similar method to Andree's that I personally use would be to put the value in row one above each corresponding checkbox column. I then use the same formula but replace the first bit with references locked to row one. In the below example, the column names are bold and underlined. Each column is set to a checkbox. In row one I then put the corresponding text.

    .

    North          South          East          West

    North           South           East           West                                                   Row 1

        Y                                     Y                        North AND East                       Row 2

                            Y                                    Y     South AND West                     Row 3

         Y                 Y                                     Y    North AND South AND West   Row 4

    .

    =JOIN(COLLECT($North$1:$West$1, [email protected]:[email protected], @cell = 1), " AND ")

    .

    It will display exactly as Andree's suggestion but moves the helper columns to row 1. Just a personal preference of mine.

    thinkspi.com

  • Mike WildayMike Wilday ✭✭✭✭✭

    Wow, that seems much cleaner. Do you then hide row 1? 

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

    Paul,

    If the solution works with your method that will absolutely be my go to set up as well.

    Really nice!

    ?

    Best,

    Andrée 

    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 ✭✭✭✭✭

    Thanks, Andree and Mike.

     

    I find it is much easier when dealing with a lot of checkboxes as opposed to needing helper columns for all of them. One of my sheets has 53 different checkbox columns filled out by a form. I initially started out using the helper columns and got frustrated, so I came up with this. The nice thing about it is that you can open the column properties and simply copy/paste the column name into the first row.

     

    I generally do not hide the row, but in the first row of the column where the JOIN(COLLECT formula is, I use bold text saying something to the effect of "THIS ROW USED FOR FORMATTING. DO NOT DELETE."

     

    I then lock the first row. If new data is being added by a form, I'll have it set so that new forms go straight to the top row. I then hide the checkbox columns. Since they're populated by forms, they don't really need to be visible on the sheet anyway.

     

    I'm glad you guys like the solution. yes

    thinkspi.com

Sign In or Register to comment.