Joining multiple values into one cell

1845sarah
1845sarah ✭✭✭✭
edited 08/01/22 in Formulas and Functions

Hello

I'm stumped on what formula I need to use to replicate the below.

I have an intake sheet where Users select a 'Service' by way of a checkbox (Development, Digital, Film).

I want to be able to join the values which have been checked in the 'Services' column/row but as a column formula.

Any ideas?

Many thanks in advance!



Best Answer

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓

    @1845sarah

    Try this

    =IF([Development] = 1, "Development")+ IF([Digital] = 1, IF( [Development] = 1, Char(10)+"Digital", "Digital")) + IF([Film] = 1, IF(OR([Digital] = 1,[Development] = 1), Char(10)+"Film", "Film"))

    It is a mix of a Text Function and some IF logic to determine if it needs to be put in a Line Break, the Char(10)

    You could do it as a nested if but then there are a lot of cases that you would have to account for this way. You simply see if it is checked and determine if a previous value being checked warrants a Line Break

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓

    @1845sarah

    Try this

    =IF([Development] = 1, "Development")+ IF([Digital] = 1, IF( [Development] = 1, Char(10)+"Digital", "Digital")) + IF([Film] = 1, IF(OR([Digital] = 1,[Development] = 1), Char(10)+"Film", "Film"))

    It is a mix of a Text Function and some IF logic to determine if it needs to be put in a Line Break, the Char(10)

    You could do it as a nested if but then there are a lot of cases that you would have to account for this way. You simply see if it is checked and determine if a previous value being checked warrants a Line Break

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!