IF Statements to JOIN or Concatenate cell values from 1 sheet into another

Davyn

Hi,

Needing some help to figure out the best way of setting up some automation....

I have a worksheet "Automated Assistant - Calling Mode". I have a bunch of workflows created to select certain items (tasks) in a multi select column "Next Action" column... See screen shot "Calling Mode sheet to show results."

So right now, this "Next Action" column has results based on workflows only... this column isn't manually edited and I want to keep it that way. Changes to this column must be automatic and based on criteria I set within the sheet.

The problem with setting it up with workflows, is I cannot remove single items (tasks) in "Next Action" column based on criteria being met.... I can only clear the entire cell or change the cell by entering new selections.

IF Statements are newish to me and I haven't used them in creating long complex rules so I haven't tried that yet.

As a possible solution I tried creating another database worksheet "Next Action List" (see attachment) which contains all of the "Next Actions," each on a separate row with a checkbox for each item. Then I can use Zapier to create the filters and criteria I need to apply true values in the checkbox column for the items I want. Then I can JOIN or Concatenate values from the "Next Action List" worksheet over to the "Calling Mode" worksheet.

But I believe there is a better way to do this all in Smartsheets, but not sure how? Any ideas?

I am ok with changing the column type from multi select to a text column, if that helps.

Thanks!

Calling Mode sheet to show results.PNG

Next Action List.PNG

Julio S.

Hi @Davyn ,

Depending on which criteria need to be satisfied to automatically fill in the relevant values in your Next Action Column, you may use the following formula as an example: 

=IF([Schedule 3 way call & send video]@row = 1, INDEX({Next Action}, 1)) + CHAR(10) + IF([Schedule 3 way call]@row = 1, INDEX({Next Action}, 2)) + CHAR(10) + IF([Manually resend 3 way video to prospect]@row = 1, INDEX({Next Action}, 3)) + CHAR(10) + IF([Manually resend 3 way call invite to prospect]@row = 1, INDEX({Next Action}, 4))

This formula assumes that the criteria for the value to be added is that the corresponding checkbox column (see screenshot below) is checked. When it isn't checked, the value will be automatically removed.

If the criteria for this to happen are more complex in your case, I would suggest working on your IF statements. Note that with view to simplify the formula and the sheet setup, I've reduced the number of columns and arguments in my formula to 4. If this seems like a possible solution, you will want to continue using the same structure for the remaining arguments.

Here I'm listing some articles that can be a good reference to replicate this formula:

• IF Function.
• INDEX Function.
• Use column formulas to apply calculations to all rows in a sheet.
• Formulas: Reference Data from Other Sheets.

I hope this can be of help.

Cheers!

Julio

Julio S.

Hi @Davyn ,

Depending on which criteria need to be satisfied to automatically fill in the relevant values in your Next Action Column, you may use the following formula as an example: 

=IF([Schedule 3 way call & send video]@row = 1, INDEX({Next Action}, 1)) + CHAR(10) + IF([Schedule 3 way call]@row = 1, INDEX({Next Action}, 2)) + CHAR(10) + IF([Manually resend 3 way video to prospect]@row = 1, INDEX({Next Action}, 3)) + CHAR(10) + IF([Manually resend 3 way call invite to prospect]@row = 1, INDEX({Next Action}, 4))

This formula assumes that the criteria for the value to be added is that the corresponding checkbox column (see screenshot below) is checked. When it isn't checked, the value will be automatically removed.

If the criteria for this to happen are more complex in your case, I would suggest working on your IF statements. Note that with view to simplify the formula and the sheet setup, I've reduced the number of columns and arguments in my formula to 4. If this seems like a possible solution, you will want to continue using the same structure for the remaining arguments.

Here I'm listing some articles that can be a good reference to replicate this formula:

• IF Function.
• INDEX Function.
• Use column formulas to apply calculations to all rows in a sheet.
• Formulas: Reference Data from Other Sheets.

I hope this can be of help.

Cheers!

Julio

Davyn

Thanks Julio! I will play with this and reply here when ready.

Davyn

This worked just perfect Julio! Thank you so much for your help.