Check Box & IF Statements
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
-
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.
-
Wow.. one big Nested IF statement..
Thanks..
-
Yep! You're welcome!
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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?
-
Hi Mike,
Correct, but I forgot to put the formula in the description before. Now it's there.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Wow, that seems much cleaner. Do you then hide row 1?
-
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 EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!