How do I display the name of each program once in a metric sheet?

Hi, I'm trying to set up a metric sheet to do stats in the background of one of my sheets.

I have set up a sheet to manage analytics requests that we receive from five different program teams. The teams use a form to submit requests for analytics for their program. The sheet contains about 200 (and growing) requests submitted by the five programs. There are multiple requests submitted by each program and additional programs are going to be added throughout the year. I want to set up a metric sheet that will collect statistics for each program and I want the metric sheet to collect the new programs automatically. I have seen this done before (I believe with a combination of index and match formulas), but I can't seem to find that resource.

Does anyone have any insight on this? Thanks!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to use a JOIN/DISTINCT cross sheet reference to pull one instance of each different program into a text string with a delimiter that won't be found in any of the programs.

    Then you would be able to parse this out either down a column or across a row (whichever you prefer).

    This would require that you "pre-fill" rows to accommodate the maximum number of different programs you anticipate.

    I will dig through my notes and see if I can find you more details on the setup.

    Are you wanting your list to be across a row or down a column?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!