Is there a way to show something similiar to treeviews in EXCEL (Not user-forms) ?
Hi
Imagine this scenario: You have a company which has 1000 employees,
organized in 10 work locations, and 50 workgroups. Each work location
can have 1..N workgroups, and a workgroup cannot span multiple work
locations.
Therefore the table would be:
LOCATION GROUP PIN
1 11 a
1 11 b
1 11 c
1 12 d
1 12 e
1 13 f
....................
2......
thus showing that location 1 has 3 groups - group 11 having 3 workers,
a, b, c, group 12 having two workers, d and e, and group 13 having only
one worker, f, and so on for all the other locations.
I'm looking for a way to allow the user to drill-down in the data as
follows:
Step 1. Select one of the 10 locations - (explode node Location), say
chose location 1
Step 2. Select one of the N workgroups pertaining to the selected
location (explode sub-node workgoup), say chose group 12
Step 3. See the list of pins (d, e), with their details.
Is there a way to do this in pure excel ? I could, of course, do it by
using VBA to program a true tree-view (to populate it at run-time by
using SQL queries), but is there a simpler way ? I looked at
PivotTables, Grouping & Oulining, (of which I admittedly knew precious
little), but I don't see a way to implement what I need in the sheet
directly.
Also, by the way, how could I run SQL queries (programatically) against
data in the same workbook ?
Thank you for your suggestions.
Alex.
|