Check Box & IF Statements

BobLonely

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

 

Mike Wilday

You would have to check for each possible combination. 

=IF(And(North@row = 1, South@row=1),"North South", IF(And(East@row=1, South@row = 1, North@row=1, West@row =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

BobLonely

Wow.. one big Nested IF statement..

 

Thanks..

Mike Wilday

Yep! You're welcome! 

Andrée Starå

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(North@row = 1; "North")    

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

=IF(North@row = 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(NH@row:WH@row; NH@row:WH@row; NOT(ISBLANK(@cell))); " AND ")    

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

=JOIN(COLLECT(NH@row:WH@row, NH@row:WH@row, 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

Mike 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(North@row= 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å

Hi Mike,

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

Best,

Andrée

Paul 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, North@row:West@row, @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.

Mike Wilday

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

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 

Paul 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.