Creating (pivot) table with double-click functionality for detailed data

[NaumanJ]

Hi there,

I am trying to create an Excel report that provides an inventory count (in table form), and for all the values in that table, I wish to have double-click functionality to access the detailed data for the selected value.

This is a rough outline of how I want the table to look. It needs to be week-over-week data for the inventory of escalations (new, resolved, active).

Fig 1.1

	Week 4	Week 3	Week 2	Week 1
New Escalations
Resolved Escalations
Active Escalations

However, I am struggling to figure out how to organize the data / which method to use in order to populate the table + show its detailed data. Here is the situation:

Fig 1.2

Order #	Customer Name	Reason Code	Status	Comments	Create Date	Create Date (Week)	Resolution Date	Resolution Date (Week)
1​	Company 1	First Code	Complete	Misc.	1-Jan-20​	Week 1, 2020	1-Feb-20​	Week 5, 2020
2​	Company 2	Second Code	Complete	Misc.	15-Jan-20​	Week 3, 2020	12-Feb-20​	Week 7, 2020
3​	Company 3	First Code	Complete	Misc.	17-Jan-20​	Week 3, 2020	2-Feb-20​	Week 5, 2020
4​	Company 4	Third Code	Active	Misc.	1-Feb-20​	Week 5, 2020
5​	Company 5	Second Code	Active	Misc.	10-Feb-20​	Week 7, 2020

Above is a sample of how the data looks when I export it from my source. Here are more details to make this make sense:

• I want to create a weekly report that shows WoW data points for the number of escalations
• To classify the values in the correct week, the "Create Date" and "Resolution Date" columns will be utilized
• I want each cell to have a numerical value that takes into account the Week and sums the new, resolved and active escalations (and when the cell is clicked on, it shows detailed data for all of the rows that led to the final value). So if the value is 10, the detailed data sheet should show the 10 rows that formed that value

What I'm trying to figure out is how do I create a pivot table (or something else) that will let me use these data points and place them in the right spot. As in, how do I create a table where the Week is the heading for the columns, and based on the Create Date and Resolution Date for each respective order, it falls into the respective week (ie. if the Order had a Resolution Date in Week 2, it should increase the sum of Week 2 Resolved Escalations by 1). But it gets more complex when I want to double-click to to access these values. So, for example, if I have "17" in Week 2 for "Resolved Escalations", if I click on the 17, it should open a new worksheet with the 17 detailed data points (which will look like Fig 1.2).

Each order can only fall into its respective week with regards to "New Escalations" and "Resolved Escalations", because it was strictly created or resolved in a particular week. However, for "Active Escalations", the order can apply to multiple weeks. So, for example, an order that was created in Week 1 but got resolved in Week 4, will show as an Active Escalation in weeks 1 through 3.

I'm confused as to how I should structure this report to achieve this end result. Should I be creating helper columns? Use binary codes to create a sum? Run some functions to make everything fall into the correct week? Use Pivot Tables?

This might seem a bit confusing, so please let me know if you have any questions. I appreciate all the support with this, thank you so much in advance. I've been trying to figure this out for a while and unfortunately have not been able to think of a good solution. Please help