Using Multiple IF Statements
Hello,
As Smartsheets does not allow for multi-select dropdown lists, I am trying to work a way around this using checkboxes and IF statements but keep running into a roadblock.
I want it to say:
If the "Chemotherapy" checkbox is selected in Column A, Column E says "Chemotherapy" . If the "ICU" column is selected in Column B, Column E says "ICU" --- I have been able to accomplish both of these using basic IF functions. But what I need it to do next is say "If "Chemotherapy" and "ICU" checkbox is selected, Column E says "Chemo, ICU"
I've tried =IF(Chemo1 = 1, "Chemo," IF(ICU1 = 1, "ICU,")) and that works for Chemo, but the IF statement takes the first item listed as the top priority, so it only works if one or the other is selected but not both.
Any thoughts?
Thanks!
Comments
-
There are a couple of different options you could do. If you're testing for multiple criteria like that, then you would have to write the IF statement to test for all your different options. That could be one insane IF statement.
I might consider making a hidden column with IF statements for each checkbox that would write in the item's name if it's selected, and then you could do a simple join to bring all the cells together. That way whatever is written in would be put into your join.
-
Try this:
=IF(AND(Chemo@row, ICU@row), "Chemo,ICU", IF(Chemo@row, "Chemo", IF(ICU@row, "ICU", "")))
Craig
-
Craig's solution works if those are the only checkboxes you have to deal with. If you start adding additional treatments it could get hairy trying to formulate all the possible combinations.
-
I agree with Mike.
But the OP said "using checkboxes" and while mentioning Col A and B being displayed in Col E implies, to me, that there may be a future requirement for Col C and D. But that may just be me.
Also, changing the Chemo text results depends on other factors than just whether Chemo@row is checked.
If it weren't for that wrinkle, I'd suggest adding a ROW to house the text version of the column, not a new column for each. Reference the row using absolute references. Even with that wrinkle, that would probably be a better solution anyway.
Craig
-
I tried out my solution without hiding the extra columns, and it's not pretty at basic attempt... there are extra commas when there is nothing in the box.
So hopefully, you don't have additional checkboxes to measure against.
See screenshot for example.
-
I honestly am leaning more towards Mike's suggestion of helper columns and a join statement. I use this in a few sheets already and love it.
The nice thing about it is that it's pretty flexible when adding or removing different checkbox options.
Just add your new choice checkbox column "NewColumn". Then add the helper column "NewColumnH". Add the IF statement to populate the text in the helper column, and as long as you put the new column before the last column on your JOIN statement, then it will automatically be included.
Note: I tend to initially use condensed column names and then throw an H on the end of it for my helper columns. I personally find it makes writing formulas a little bit easier. After I get everything working, I change the column names to what I really want them to be and it's automatically updated in the formulas.
-
I just wish there was a way to get around the , , , on cells where there is no data. I bet the formula could be rewritten to ignore blank columns, but then that defeats the quickness and ease of this process. It would look cleaner if spaces were used. See Screenshots.
-
=JOIN(COLLECT(A@row:D@row, A@row:D@row, NOT(ISBLANK(@cell))), ",")
Craig
-
Good call. I usually end up working a SUBSTITUTE in somewhere, but I like this better.
-
You are welcome.
I really should post my one-liners.
Craig
-
Thanks for your suggestions, everyone! I ended up using a combination of Mike's idea of using JOIN, and an old post from Craig for replacing carriage separators. The final product looks very nice!
-
I'm glad we were able to come up with a workable solution for you. Enjoy!
-
I have actually been using this A LOT now and love it. So simple and clean, but flexible as well. It has replaced the end result of a lot of my time calculation formulas.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!