IF statement that can pull data from more than one cell if

hannahstayo

We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the "primary issue category", and then based on the issue they choose, it gives them another drop down where they can choose a "secondary issue" where they can be more specific about what the issue is.

In the data set, I have used an IF formula in the "secondary issue" column to pull data from the helper columns depending on which "primary issue category" has been selected. This works without issue and I have included the formula below.

=IF([Issue category]@row = "Maintenance issue", [Maintenance issue]@row, IF([Issue category]@row = "Housekeeping issue", [Housekeeping issue]@row, IF([Issue category]@row = "Guest request", [Guest request]@row, IF([Issue category]@row = "Keys and access issue", [Keys and access issues]@row, IF([Issue category]@row = "Lift issue", [Lift issues]@row, IF([Issue category]@row = "Security", [Security issue]@row))))))

But there will be times when a guest complains about multiple things, and instead of splitting these out into multiple rows, I would like to keep it on a single row (one row per guest). I have made it so that multiple options can be selected for "primary issue category", but is there anyway that I can pull data from more than one helper column into the "secondary issue" column?

Paul Newcome

Try this:

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> ""), CHAR(10))

Paul Newcome

Are you able to provide screenshots for context?

hannahstayo

Hi Paul, yes of course. Here is a screenshot showing the "Primary issue category" column, the "Secondary issue category" column and then all the helper columns (these all relate to one of the drop down options in "Primary issue category"). The formula in "Secondary issue category" is working fine in the first 5 rows where there is only 1 option selected in "Primary issue category". It's the last line where two issues have been selected in the "Primary issue category" where I am having issue pulling the data from the two helper columns. Just to say, it is possible that we will have up to 6 options selected in the "Primary issue category" so we may need to pull from all 6 helper columns, although I think that is rare. I reckon most times if it happens we would need to pull from 2 or 3 at most.

Hopefully the screenshot below works, but let me know if you need anything else.

Paul Newcome

Try this:

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> ""), CHAR(10))

Nick Korna

Hi @hannahstayo ,

If you set the primary issue category dropdown to allow multiple selections, you can use the logic portion of the form to allow multiple things to be entered at once:

Example:

For combining the issues within the sheet itself, you can use the JOIN function. You can either have this very simple with just a space as a delimiter (as an example):

=JOIN([Maintenance issue]@row:[Security issue]@row, " ")

or with the addition of a helper cell or summary you can have a line break (using a summary as the helper in this example):

=JOIN([Maintenance issue]@row:[Security issue]@row, SUBSTITUTE(Helper#, "-", " "))

Hope this helps, but if you've any issues/questions then just post! 😊

hannahstayo

Hi both,

Thanks for your help with this and getting back to me so quickly. @Paul NewcomeAll three solutions worked, and I have made a note for the future as I am sure I will need to do something like this again!

Paul Newcome

@hannahstayo Happy to help. 👍️

@Nick Korna Two notes:

When using just a basic JOIN function, it will repeat the delimiter for all blank cells. So if you used a comma as a delimiter instead of a space, you could end up with "Cell 1 Value,,,,,,,,,". That's why I personally like to use the JOIN/COLLECT to exclude the blank cells.

You can also use CHAR(10) for the line break. It gets rid of the need for a helper cell as well as the SUBSTITUTE function.

Nick Korna

Thanks @Paul Newcome, I wasn't aware on the line break char so good to learn how to do that!

hannahstayo

Hi @Paul Newcome one question on the formula you shared. Is there a way to have these all show in a single line, with a comma splitting them? I am using Zapier to send the data to Slack (as Smartsheets Slack integration isn't good enough for me to use it yet), and because the options are all on a separate line in the cell, it's affecting the format of the Slack message.

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> ""), CHAR(10))

Paul Newcome

@hannahstayo You would adjust the delimiter portion of the JOIN function.

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> ""), CHAR(10))

hannahstayo

@Paul Newcome Perfect thanks! Wasn't 100% which part I had to change, but that's working now

Paul Newcome

Happy to help. 👍️