hierarchys - can you build an elegant hierarchy using vba
 

hi all

a question to all you experts - is it possible to build a basic hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a drill up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

i should explain that my lsit is simple in nature

lets say


4 mm bar
8 mm bar
12 mm bar

all reporting up to bars which reports up to Total Steel

i.e a classic OLAP hierarcy ( sort of analysis server territory )

how can we do this in excel
--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a drill up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

You can do quite a bit with XL and VBA as long as you have the time and/or
money to invest in it.

For a drilldown example see
PivotChart Drill-down
http://www.tushar-mehta.com/excel/so...own/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
experts - I assume excel can not do this ...............

can anyone help ??

hierarchys - can you build an elegant hierarchy using vba
 

i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing this
naturally ) but i suspect i need an OCX control to help me - it must have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

Christopher,

What does this translate to in terms of Excel ? I understand the concept
but what should the resulting excel file be able to *do* ? Is it a tool for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing this
naturally ) but i suspect i need an OCX control to help me - it must have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

Dear Tim

sorry if i confussed the issue - all i want is to have a list of product
codes
in a data range lets say B10:B59 and be able to say this code reports to
Bars or Plates - i.e build a parent child relationship of course many levels
deep so i need my hierarchy to have a level number , a parent or child
relationship , a description - thus at any stage i can drill up or down on
any given member in a list box for example

i guess im trying to find an OCX or another item to add to the VBA toolkit

chris

--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the concept
but what should the resulting excel file be able to *do* ? Is it a tool for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing this
naturally ) but i suspect i need an OCX control to help me - it must have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

i should also add if I have a hierarchy on products i need code to add or
delete or move any given item in the hierarchy - this functionality will be
added to a system i have written in Excel 2003 and needs to be user friendly
- so far the systems works but i see no easy way for hierarchy generation
over many dimensions even if they only have single hierarchys - pivot table
does not help me - i need raw codde to create the relationships thats easy to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the concept
but what should the resulting excel file be able to *do* ? Is it a tool for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing this
naturally ) but i suspect i need an OCX control to help me - it must have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i should also add if I have a hierarchy on products i need code to add or
delete or move any given item in the hierarchy - this functionality will
be
added to a system i have written in Excel 2003 and needs to be user
friendly
- so far the systems works but i see no easy way for hierarchy generation
over many dimensions even if they only have single hierarchys - pivot
table
does not help me - i need raw codde to create the relationships thats easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the
concept
but what should the resulting excel file be able to *do* ? Is it a tool
for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing
this
naturally ) but i suspect i need an OCX control to help me - it must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote
in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at the
lowest level maybe 500 products although this figure may be 50 depending on
who uses the sheet , lets say 50 codes who have 30 parents who have 4 parents
with a master code at top of hierarchy


--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i should also add if I have a hierarchy on products i need code to add or
delete or move any given item in the hierarchy - this functionality will
be
added to a system i have written in Excel 2003 and needs to be user
friendly
- so far the systems works but i see no easy way for hierarchy generation
over many dimensions even if they only have single hierarchys - pivot
table
does not help me - i need raw codde to create the relationships thats easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the
concept
but what should the resulting excel file be able to *do* ? Is it a tool
for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing
this
naturally ) but i suspect i need an OCX control to help me - it must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote
in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

best example could be

red balls
yellow balls
green balls

all reporting to snooker which in turn reports to games
--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i should also add if I have a hierarchy on products i need code to add or
delete or move any given item in the hierarchy - this functionality will
be
added to a system i have written in Excel 2003 and needs to be user
friendly
- so far the systems works but i see no easy way for hierarchy generation
over many dimensions even if they only have single hierarchys - pivot
table
does not help me - i need raw codde to create the relationships thats easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the
concept
but what should the resulting excel file be able to *do* ? Is it a tool
for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents - i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing
this
naturally ) but i suspect i need an OCX control to help me - it must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward" wrote
in
message ...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

A treeview is like the "folder" view in windows explorer. Isn't that the
kind of thing you'd need in order for your user to visualize the connections
they're making?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...


what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at the
lowest level maybe 500 products although this figure may be 50 depending
on
who uses the sheet , lets say 50 codes who have 30 parents who have 4
parents
with a master code at top of hierarchy


--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i should also add if I have a hierarchy on products i need code to add
or
delete or move any given item in the hierarchy - this functionality
will
be
added to a system i have written in Excel 2003 and needs to be user
friendly
- so far the systems works but i see no easy way for hierarchy
generation
over many dimensions even if they only have single hierarchys - pivot
table
does not help me - i need raw codde to create the relationships thats
easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the
concept
but what should the resulting excel file be able to *do* ? Is it a
tool
for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote
in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents -
i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing
this
naturally ) but i suspect i need an OCX control to help me - it
must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward"
wrote
in
message
...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a
basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to
build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

dear tim sorry missed your reply

yes a treeview would be excellent - especially if it could add or delete new
members and/or move the members to different places

for any hierarchy i always want a level , a name , a member , a parent or
child - property against any member in the tree

so can excel do this or do we have an OCX control - as yet i have not seen
one and i am feeeling excel cannot do this elegantly
--
C Ward


"Tim Williams" wrote:

A treeview is like the "folder" view in windows explorer. Isn't that the
kind of thing you'd need in order for your user to visualize the connections
they're making?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...


what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at the
lowest level maybe 500 products although this figure may be 50 depending
on
who uses the sheet , lets say 50 codes who have 30 parents who have 4
parents
with a master code at top of hierarchy


--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
i should also add if I have a hierarchy on products i need code to add
or
delete or move any given item in the hierarchy - this functionality
will
be
added to a system i have written in Excel 2003 and needs to be user
friendly
- so far the systems works but i see no easy way for hierarchy
generation
over many dimensions even if they only have single hierarchys - pivot
table
does not help me - i need raw codde to create the relationships thats
easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand the
concept
but what should the resulting excel file be able to *do* ? Is it a
tool
for
creating the relationships, displaying the relationships, or what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote
in
message ...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and grandparents -
i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP doing
this
naturally ) but i suspect i need an OCX control to help me - it
must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward"
wrote
in
message
...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a
basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like to
build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

There is a treeview component you can use in VBA - if not in the toolbox
then right-click and choose "additional components" and look for "Microsoft
treeview..."

It will not hold all of the specific properties you list, but you should be
able to hold those in a collection of objects or similar construct and just
use the treeview to display the realtionships.

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
dear tim sorry missed your reply

yes a treeview would be excellent - especially if it could add or delete
new
members and/or move the members to different places

for any hierarchy i always want a level , a name , a member , a parent or
child - property against any member in the tree

so can excel do this or do we have an OCX control - as yet i have not seen
one and i am feeeling excel cannot do this elegantly
--
C Ward


"Tim Williams" wrote:

A treeview is like the "folder" view in windows explorer. Isn't that
the
kind of thing you'd need in order for your user to visualize the
connections
they're making?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...


what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at
the
lowest level maybe 500 products although this figure may be 50
depending
on
who uses the sheet , lets say 50 codes who have 30 parents who have 4
parents
with a master code at top of hierarchy


--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote
in
message ...
i should also add if I have a hierarchy on products i need code to
add
or
delete or move any given item in the hierarchy - this
functionality
will
be
added to a system i have written in Excel 2003 and needs to be
user
friendly
- so far the systems works but i see no easy way for hierarchy
generation
over many dimensions even if they only have single hierarchys -
pivot
table
does not help me - i need raw codde to create the relationships
thats
easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand
the
concept
but what should the resulting excel file be able to *do* ? Is
it a
tool
for
creating the relationships, displaying the relationships, or
what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward"
wrote
in
message
...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and
grandparents -
i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP
doing
this
naturally ) but i suspect i need an OCX control to help me -
it
must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward"

wrote
in
message
...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a
basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like
to
build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!

hierarchys - can you build an elegant hierarchy using vba
 

thank you i will work with this - it might do the job


--
C Ward


"Tim Williams" wrote:

There is a treeview component you can use in VBA - if not in the toolbox
then right-click and choose "additional components" and look for "Microsoft
treeview..."

It will not hold all of the specific properties you list, but you should be
able to hold those in a collection of objects or similar construct and just
use the treeview to display the realtionships.

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...
dear tim sorry missed your reply

yes a treeview would be excellent - especially if it could add or delete
new
members and/or move the members to different places

for any hierarchy i always want a level , a name , a member , a parent or
child - property against any member in the tree

so can excel do this or do we have an OCX control - as yet i have not seen
one and i am feeeling excel cannot do this elegantly
--
C Ward


"Tim Williams" wrote:

A treeview is like the "folder" view in windows explorer. Isn't that
the
kind of thing you'd need in order for your user to visualize the
connections
they're making?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote in
message ...


what is a treeview ?

I guess at most a product dimension for me may have 4 levels and at
the
lowest level maybe 500 products although this figure may be 50
depending
on
who uses the sheet , lets say 50 codes who have 30 parents who have 4
parents
with a master code at top of hierarchy


--
C Ward


"Tim Williams" wrote:

Would a treeview approach work in this context?
What volume of data - 100's, 1000's of records?

Tim

--
Tim Williams
Palo Alto, CA


"christopher ward" wrote
in
message ...
i should also add if I have a hierarchy on products i need code to
add
or
delete or move any given item in the hierarchy - this
functionality
will
be
added to a system i have written in Excel 2003 and needs to be
user
friendly
- so far the systems works but i see no easy way for hierarchy
generation
over many dimensions even if they only have single hierarchys -
pivot
table
does not help me - i need raw codde to create the relationships
thats
easy
to
use - i can discuss offline if needed


--
C Ward


"Tim Williams" wrote:

Christopher,

What does this translate to in terms of Excel ? I understand
the
concept
but what should the resulting excel file be able to *do* ? Is
it a
tool
for
creating the relationships, displaying the relationships, or
what ?

Tim.

--
Tim Williams
Palo Alto, CA


"christopher ward"
wrote
in
message
...
i have a sheet with a number of product codes as an example
using a hierarchy tool i want to create parents and
grandparents -
i.e
a
basic olap hierarchy on a dimension ( im used to Oracle OLAP
doing
this
naturally ) but i suspect i need an OCX control to help me -
it
must
have
been done before


--
C Ward


"Tim Williams" wrote:

What is it that you want your drill up/down function to do?

Tim

"christopher ward"

wrote
in
message
...
experts - I assume excel can not do this ...............

can anyone help ??

--
C Ward


"christopher ward" wrote:

hi all

a question to all you experts - is it possible to build a
basic
hierarchy
using vba ( I know Vb has an object in its toolbox )

or does anyone have an ocx etc ??

my sheet has a range of product values and i would like
to
build
a
drill
up
and drill down function usinf standard excel

if you reply - thanks in advance


--
C Ward - a novice user !!!!