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

NaumanJ

New Member
Joined
Mar 17, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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 4Week 3Week 2Week 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 NameReason CodeStatusCommentsCreate DateCreate Date (Week)Resolution DateResolution Date (Week)
1​
Company 1First CodeCompleteMisc.
1-Jan-20​
Week 1, 2020
1-Feb-20​
Week 5, 2020
2​
Company 2Second CodeCompleteMisc.
15-Jan-20​
Week 3, 2020
12-Feb-20​
Week 7, 2020
3​
Company 3First CodeCompleteMisc.
17-Jan-20​
Week 3, 2020
2-Feb-20​
Week 5, 2020
4​
Company 4Third CodeActiveMisc.
1-Feb-20​
Week 5, 2020
5​
Company 5Second CodeActiveMisc.
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 :(
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,119,114
Messages
5,576,188
Members
412,703
Latest member
sainayzawhtwe
Top