Working with Excel 2007. Attached is a sample worksheet with all of the sample data and desired result.
I have a worksheet ("Data") that lists schools with students who have had
one or more problems:
School Student Issue
-----------------------------------------------
ABC Middle Bobby Detention
ABC Middle Bobby Suspension
ABC Middle John Detention
ABC Middle Mary Multiple absenses
XYZ Elem Kevin Detention
XYZ Elem Lisa Multiple absenses
etc.
For example, 3 students have Detention as a problem, 1 student has
Suspension as a problem, etc.
I use a pivot table to summarize the data like so:
Issue Number of Students with Problem
------------------------------------------
Detention 3
Suspension 1
Absenses 2
I realize pivot tables have a built-in drill down feature where you can
double click a value and expand to show values nested within it, etc. But
I'd like a different interface which I think will require VBA.
I would like the user to be able to drill down into this data like so:
1. In the above table, click on an issue (e.g., "Detention") and, below the
table they just clicked on, table appears showing a list of Schools and the
number of students in that school with that particular problem:
School Number of Students with Problem
------------------------------------------
ABC Middle 2
XYZ Elem 1
In other words, the VBA needs to detect the value selected (e.g.,
"Discipline"), and then extract from the "Data" worksheet the list of
schools and the count of students with "Discipline" as a problem.
2. In this new table, click on a school (e.g., "ABC Middle") and, below
this table, a table appears showing a list of students in that school with
the problem.
Student
------------------------------------------
Bobby
John
In other words, the VBA needs to detect the value selected (e.g., "ABC
Middle"), and then extract the list of students from "ABC Middle" with
"Discipline" a problem.
Obviously, if the user goes back to a previous table and makes a different
selection, the tables that follow should refresh to show the applicable
data.
Over time the data will change. The number of columns will always be the
same (for both tables), but the number of rows will vary.
Any suggestions or links about the best way to approach this?
Thank you.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Dynamic tables.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
