How can I display data horizontally from data in a row format

carpenb
carpenb ✭✭✭
edited 11/15/24 in Formulas and Functions

I have metric data on a project that I am capturing weekly that I would like to view horizontally week over week for the current month. I'd like to do this dynamically so I can pull the data into a dashboard. This is what I have in my metrics sheet:

This is how I would like the data displayed:

Any advice you can provide to help me get this data into this format to show the monthly trend would be most appreciated!

Best Answer

  • Courtney S.
    Courtney S. ✭✭✭✭✭
    Answer ✓

    In your first sheet, I think you'll need to add a column labelling the week # column that you want each row reported in. Then you can use the INDEX/COLLECT formula mentioned here:

    Formula combinations for cross sheet references | Smartsheet Learning Center

    See the header "Lookup one cell using multiple criteria"

    You would fill in your Week 1, Week 2, etc. columns with INDEX/COLLECT formulas.

    Your Collect function would go look at the Primary column (Project name) and the Week # column that you'll need to add. Your Index function would pull the Engagement column.

Answers

  • Courtney S.
    Courtney S. ✭✭✭✭✭
    Answer ✓

    In your first sheet, I think you'll need to add a column labelling the week # column that you want each row reported in. Then you can use the INDEX/COLLECT formula mentioned here:

    Formula combinations for cross sheet references | Smartsheet Learning Center

    See the header "Lookup one cell using multiple criteria"

    You would fill in your Week 1, Week 2, etc. columns with INDEX/COLLECT formulas.

    Your Collect function would go look at the Primary column (Project name) and the Week # column that you'll need to add. Your Index function would pull the Engagement column.

  • carpenb
    carpenb ✭✭✭

    @Courtney S. Thank you for your response. I followed your advice and was able to get the data formatted in the way I needed!! I created a WeekNumber column for each item in my Sample Metric Page added a WeekNumber column to my second sheet and used that field and the name of the project with the INDEX and COLLECT formula to populate the fields I needed:

    =INDEX(COLLECT({Sample Metric Page Metric Range}, {Sample Metric Page Project_Name Range}, [Primary Column]@row, {Sample Metric Page WeekNumber}, [Week Number]1), 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!