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

carpenb

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!

Courtney S.

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.

Courtney S.

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

@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)