| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 !!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ?? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 !!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Elegant Formulas | Excel Worksheet Functions | |||
| Is there an elegant way? | Excel Discussion (Misc queries) | |||
| More elegant way to do IF () | Excel Worksheet Functions | |||
| More elegant method? | Excel Worksheet Functions | |||
| How to build build a macro that automatically imports | Excel Programming | |||
Linear Mode
