Hi

1. 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 list 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.

2. Also, by the way, how could I run SQL queries (programatically)
against data in the same workbook ?

Thank you for your suggestions.

Alex.

Radu

1. use auto filter (Data|Filter|Auto Filter) and that should work
2. try dlookup for a single cell or look at ado (search the web for examples
on sql in excel)
--
Hope this helps
Martin Fishlock


"Radu" wrote:

Hi

1. 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 list 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.

2. Also, by the way, how could I run SQL queries (programatically)
against data in the same workbook ?

Thank you for your suggestions.

Alex.

Thanks, Martin

1. Unfortunately this won't work - the user has to be able to select a
workgroup, 'explode' it, and view its details as well as all the
subgroups, employees, etc, with their details - something similar to,
what Explorer is for files.

Thanks again.
Alex.

Martin Fishlock wrote:
Radu

1. use auto filter (Data|Filter|Auto Filter) and that should work
2. try dlookup for a single cell or look at ado (search the web for examples
on sql in excel)
--
Hope this helps
Martin Fishlock


"Radu" wrote:

Hi

1. 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 list 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.

2. Also, by the way, how could I run SQL queries (programatically)
against data in the same workbook ?

Thank you for your suggestions.

Alex.