Formula to collect column names if there is a "1" present

I have a pivot table that I am attempting to use in order to collect a list of all jobs that our subcontractors have/are working on. Basically, I want a roll up of all jobs that each sub has worked on. The sheets where this info is held are all on individual sheets, which is the reason I used a pivot table to try and get everything on a single sheet.

The rows are each subcontractor and the columns are the names of my jobs If a sub is working on that job, the column will have a "1". I assume I need some kind of Join(Collect( formula, but unsure how to snag the name of the columns without creating 100+ extra 'helper' columns.

This is what I am looking for the formula to do:



Best Answer

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

    You would need to create a helper row where the jobs (column names) were listed out. Then you would be able to use a JOIN/COLLECT to pull them.

    If you had the helper row on row 1, then the formula would look something like this...

    =JOIN(COLLECT([1st Job Column]1:[5th Job Column]1, [1st Job Column]@row:[5th Job Column]@row, @cell = 1), ", ")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!