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

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,

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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(Auto@row, Auto:Auto, 0)


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

    =IF(Row@row = 1, "helper text, column name, or whatever you want in the top row", desired_column_formula)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!