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
-
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
-
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.
-
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?
-
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.
-
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)
-
It worked!! Thank you both!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!