IFs from a LIST

Options

I have a series of checkboxes on my form that help a client scope a project. Each checkbox is a column then in Smartsheet. I'd like to easily create a strong that is a list of the boxes checked.

Names of Checkbox Columns

[Choice1], [Choice2], {Choice3], [Choice 4], ...

Each checkbox on the form has a label that is a specific aspect of the scope. So for the four above, the labels could be

  1. Addressing changes to program Assessment Plan (e.g., rubrics)
  2. Applying results from recent Program Review
  3. Changing course length and/or number of modules
  4. Changing or adding a third-party tool (integration)

Is there a way in a new column to write a formula that will easily combine the labels based on checkboxes?

So, for example, if the client checks Choice1, Choice3, and Choice4 (not Choice2), the cell in the new column would display "Addressing changes to program Assessment Plan (e.g., rubrics); changing course length and/or number of modules; changing or adding a third-party tool (integration)".

I know I could use a bunch of IF statements with + between each, but is there a way to evaluate all the checkboxes, and if true, add certain text?

Like

=IF(TRUE([Choice1], [Choice2], {Choice3], [Choice 4]), ""Addressing changes to program Assessment Plan (e.g., rubrics)", "Applying results from recent Program Review, "Changing course length and/or number of modules, "Changing or adding a third-party tool (integration)")

I know this not correct at all, but the idea is for each [Choice] checkbox, if true, it adds the text based on the position in the string of text. Choice1 goes with "Addressing changes to program Assessment Plan (e.g., rubrics)", Choice2 goes with "Applying results from recent Program Review", Choice3 goes with third item in list, etc.

For my form, I actually have 9 checkboxes, so looking for the easiest way to assess True/False for all (in a syntax that is easy to update if needed).

Along these lines, is there a way to force them all to be in LOWER case as well, separated by a semi-colon?

Thanks!!

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/07/21
    Options

    Hi @aschneiderheinze74206

    Hope you are fine, yes you can do that in simple way by using an additional helper sheet contains your 9 choices ( this option will give you the flexibility to add more choices in future ) and in your main sheet you can use a Join & Index & collect function in one formula to collect and create the text using your choices.

    if you like i can create it for you but i need to have access on sample sheet with admin permission  (after removing or replacing any sensitive information) to create the exact formula and test it with you.

    My Email: Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!