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

Linda F

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 !

L_123

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

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 !

L_123

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

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

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

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

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

L_123

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

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 !