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.

In the main menu select
DataFilterAuto Filter


"Radu" wrote:

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.

Take a look at Pivot Tables and their functionality.

I think, in this situation, the key is NOT to make Excel do what you
envision, but to exploit what Excel can do to get the information you want.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Radu" wrote:

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.

Hi, and thanks for your replies.

I don't know how to use Pivot tables for this purpose - I need to allow
the user to click on a workgroup, see some aggregate data for that
workgroup (say, # of employees, # of hours worked by all employees in
that workgroup, etc), and also explode the workgroup to show details of
the employees belonging to it - in other words, something similar to
what Explorer shows for files/folders.

Thanks.
Alex. Nitulescu


Ron Coderre wrote:
Take a look at Pivot Tables and their functionality.

I think, in this situation, the key is NOT to make Excel do what you
envision, but to exploit what Excel can do to get the information you want.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Radu" wrote:

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.