I need to result the column's name when using a JOIN(COLLECT) or INDEX(MATCH) formula

NutritionGal2005

Previously, Row 1 of my smartsheet contained the column's name. This way, I could write a JOIN(COLLECT) formula to return the column's name if certain conditions were met throughout the sheet for that row. That worked until we applied column formulas to some of the rows. Now Row 1 can't be the column's name because of the column formula. I see one of two solutions.

How do you exclude Row 1 from a column formula?

OR

How do you return the column name in a JOIN(COLLECT) formula? and an INDEX(MATCH) formula?

Thank you,

Paul Newcome

Yes. I have had to use this when clients wanted to include help text on the top row of the sheet but needed column formulas elsewhere.

I use an auto-number column (called "Auto" in this example) and a text/number column (called "Row" in this example) to get the row numbers as usable values on the sheet. The column formula in the "Row" column is:

=MATCH([email protected], Auto:Auto, 0)

Then in any column where I needed a column formula, I would use what@Carson Penticuffsuggested.

=IF([email protected]= 1, "helper text, column name, or whatever you want in the top row", desired_column_formula)

Carson Penticuff

I don't believe either of those options are directly possible.

As a very messy workaround, you can add a helper column that does nothing but has something listed in row one, "1" for example. Then you can include in all column formulas something such as IF([Helper]@row, 1, "Column Name", ***What you want to do here***)

I don't like this, but it would work.

NutritionGal2005

Thanks for the idea. To clarify is "Column Name" in your answer above a code to pull the actual column name, or a place holder for whatever I would have typed as the column name in row 1?

Carson Penticuff

Just a placeholder. You would have to manually replace the column name for each formula. To the best of my knowledge, there is no way to pull the column name without maybe some type of addon or using the api.

Paul Newcome

Yes. I have had to use this when clients wanted to include help text on the top row of the sheet but needed column formulas elsewhere.

I use an auto-number column (called "Auto" in this example) and a text/number column (called "Row" in this example) to get the row numbers as usable values on the sheet. The column formula in the "Row" column is:

=MATCH([email protected], Auto:Auto, 0)

Then in any column where I needed a column formula, I would use what@Carson Penticuffsuggested.

=IF([email protected]= 1, "helper text, column name, or whatever you want in the top row", desired_column_formula)

NutritionGal2005

It worked!! Thank you both!!

Paul Newcome

Happy to help.️