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!

Find more posts tagged with

Formulas

Comments

Mike Wilday

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.

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

J. Craig Williams

Try this:

=IF(AND(Chemo@row, ICU@row), "Chemo,ICU", IF(Chemo@row, "Chemo", IF(ICU@row, "ICU", "")))

Craig

Mike Wilday

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.

J. Craig Williams

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

Mike Wilday

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.

Paul Newcome

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.

Mike Wilday

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.

J. Craig Williams

=JOIN(COLLECT(A@row:D@row, A@row:D@row, NOT(ISBLANK(@cell))), ",")

Craig

Paul Newcome

Good call. I usually end up working a SUBSTITUTE in somewhere, but I like this better.

Mike Wilday

@Craig - Nice one. I was thinking about using collect last night to do the same thing but ran out of time to test. I like it, especially with the NOT(ISBLANK(@ROW))

J. Craig Williams

You are welcome.

I really should post my one-liners.

Craig

Torri B.

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!

Mike Wilday

I'm glad we were able to come up with a workable solution for you. Enjoy!

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…