How do I use @row in an IF formula when I am referencing many columns for 1 response?

Options

I created a plan with 8 goals, each goal has their own objectives (range from 3-11 objectives each goal, and each objective has their own strategies (range from 3-12 strategies each objective). I want to reference any one of the 42 columns I created for the form logic so drop downs would reflect in the objective or strategy column. I used this formula but it doesn't work - this means I have to reference each objective and each strategy in that specific column. An Example is below:

Please help !

Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/19/20 Answer ✓
    Options

    So basically only one cell in each of the groups will be populated in a single row, the columns will change because that is how you built dynamic dropdowns. Ok.

    Try using a join() function without a delimiter

    it would be something like

    =join(X@row:Y@row)

    *Note there is no comma in this equation

    You need to have the column sections grouped together from like columns, and replace the X and Y with the starting and ending column, and build one for each of your criteria.

  • Linda F
    Linda F ✭✭✭✭✭
    Answer ✓
    Options

    The formula =JOIN(COLLECT([First Selection Column]@row:[Last Selection Column]@row, [First Selection Column]@row:[Last Selection Column]@row, @cell <> ""), ", ") WORKED !!!

    I grouped by column all the objectives and all the strategies so this was an easy fix. Thank you so much !

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    What doesn't work for you in that formula? I don't see anything incorrect in the syntax. Can you post what the result of that formula is?

    If you are just trying to return the first non-blank value there is a simpler way to find it. Patterns in data can really help shorten formulas, so if you can give a little more detail on what you need we can help.

  • Linda F
    Linda F ✭✭✭✭✭
    Options

    It works but only for Enrollment Objectives - what if someone fills out their forms with a different objective? If they pick Learning Objective? How do I create the formula to capture the Objective regardless of choice? I created 42 different columns for the form as the logic doesn't allow you to have the ability of dynamic dropdown options in conditional form logic. There are multiple columns it must sort through to populate the the correct Objective for a goal.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/19/20
    Options

    Ah I understand. I was thinking vertically while you have the data horizontal. Is there a pattern to the data? For example, do you want to return the rightmost value? or do you want all of the values that aren't blank returned to a single cell?

    Can you tell me a bit more about the data? For example, do the columns you want returned have anything unique about them that is consistent we could use for text parsing?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I agree with @L@123. Patterns in data can be very helpful.

    So can the location of the data.

    If you have 42 different columns and you only need to pull whichever one(s) was/were selected, as long as all of the columns are next to each other you could use something along the lines of

    =JOIN(COLLECT([First Selection Column]@row:[Last Selection Column]@row, [First Selection Column]@row:[Last Selection Column]@row, @cell <> ""), ", ")

  • Linda F
    Linda F ✭✭✭✭✭
    edited 05/19/20
    Options

    I would like the correct value returned to a single cell. Example below of what I would like to see,

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/19/20 Answer ✓
    Options

    So basically only one cell in each of the groups will be populated in a single row, the columns will change because that is how you built dynamic dropdowns. Ok.

    Try using a join() function without a delimiter

    it would be something like

    =join(X@row:Y@row)

    *Note there is no comma in this equation

    You need to have the column sections grouped together from like columns, and replace the X and Y with the starting and ending column, and build one for each of your criteria.

  • Linda F
    Linda F ✭✭✭✭✭
    Answer ✓
    Options

    The formula =JOIN(COLLECT([First Selection Column]@row:[Last Selection Column]@row, [First Selection Column]@row:[Last Selection Column]@row, @cell <> ""), ", ") WORKED !!!

    I grouped by column all the objectives and all the strategies so this was an easy fix. Thank you so much !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!