VBA Cell DrillDown

[EliseFreedman]

Hi There

I am in the middle of constructing a team tracker which will be used to track progress on all our projects for example what is overdue, on track, not started. I have got a page per project listing any actions, the actionee and the status. Some of the actions may be listed against more than 1 actionee. I have then got a summary page which consists of 3 tables - overdue, on track and not started. the Team members names are listed on the left hand side and the projects are listed across the top. I have then used Sumproduct and IsNumber to calculate the number of actions against each person for that category regardless of whether or not they are the only actonee

e.g Overdue
Project 1 Project 2 Project 3
Team Member 1 2 1 0
Team Member 2 3 0 4
Team Member 3 4 2 7

I would now like to be able to add functionality whereby when you click on cell containing the number of actions for the chosen project and team member, it expands to show the detail of the actions in the rows below, a bit like when you click on a pivot table. I didnt go the pivot table route because i wanted to be able to split out actions that were against more than 1 person. Is this possible and if so how would you do it. I have come across similar for expanding columns to show detail but that wouldnt work for my scenario.

 

[SkipVought]

hi,

So how is the data in the overdue table related to some other table that has the detail?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EliseFreedman]

the details of the overdue actions and all the other actions are help in separate spreadsheets for each project

For example my page for project 1 looks like this

task, comments, actionee, target date, timescale, status(i.e overdue) RAG Status

It will list all the actions some of which will have more than one actionee (whence why I havnt used a pivot).

What i basically want to do is if im looking in the summary for Project 1, team member 1 over due actions I can click on the cell holding the amount of actions and it will expand and the detail behind the number ie the task, comments, actionee, target date etc will be pasted into the cells. The user can then click again and the detail will disappear

 

[SkipVought]

the details of the overdue actions and all the other actions are help in [red]separate spreadsheets for each project[/red]

So you want to make it [red]difficult[/red], I see.

I would VERY STRONGLY urge you to redesign your workbook. These tables ought to be consolidated into one table. It will make life much simpler!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

ONE table...
[tt]
ProjectID, task, comments, actionee, target date, timescale, status(i.e overdue) RAG Status
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}