How to extract information through treeview data?

[ankur]

I have got 2 worksheets, one has got data in Tree View, what I am supposed to do is that to fill the other worksheet based on the value of every leaf-node in treeview worksheet.


Treeview data:

X>

|X1

||-a1

||-a2

||-a3

X2

||-b1

||-b2

||-b3

X3

||-c1

||-c2

||-c3


In second sheet I want values of all parent of each nodes in columns, like for b2

Column 1| Column 2|Column 3| Column 4

B2 | X2 | X

 

[prasaddn]

Hi, I am sorry, I am not able to understand.. it is not clear, can you give more info.

 

[NARAYANK991]

Hi Ankur ,


If you are interested to know something more about Treeview controls , beyond your immediate needs , look at the resources in this link :


http://xbrl.squarespace.com/journal/2011/10/1/using-treeview-controls-in-excel.html


Narayan

 

[prasaddn]

Seriously... nothing I could understand even after viewing the links


May be chandoo can come up with his next article on this in his trademark style.


Regards,

Prasad DN

 

[Luke M]

ankur,


For ease is shortening later formulas, I used a helper column in col B. Formula in B2 is:

=MAX(IF(A2=Sheet1!$A$1:$C$20,COLUMN(Sheet1!$A$1:$C$20)))


This is an array formula that needs to be confirmed using Ctrl+Shift+Enter, not just Enter. The purpose of this formula is to find the correct column that the node call=out is in. Then, in C2:

=IF($B2-COLUMN(A$1)<=0,"",INDEX(Sheet1!$A:$E,MATCH("ZZZZ",INDEX(Sheet1!$A:$E,,$B2-COLUMN(A$1))),$B2-COLUMN(A$1)))


Copy this formula to the right equal to the number of max node levels (or a reasonable amount). Formula will display blank if it is not needed. Then copy down for was many node records as you are trying to fill.


Prasaddn,

The easiest way to think about what a treeview is, is to think of how the folders look in Windows Explorer where each level is indented. OP's original data looks like this:

[pre]

X
X1
A1
A2
A3
X2
B1
B2
B3
X3
C1
C2
C3

[/pre]
and we need a way to read that into a single line, showing all the parent structures.

 

[NARAYANK991]

Hi All ,


This is for everyone to scrutinise and criticise and enhance !


https://docs.google.com/open?id=0B0KMpuzr3MTVNGM2OWY1NjctMjlkMi00NWM5LTk5ZWUtOWI4Mzk0YjRjY2Jl


The treeview object is located on Sheet2 , and already populated with some nodes. The Root Node is the top-most node , and all other nodes are child nodes ( leaf nodes ) , some of them also being Parent nodes.


The procedure is Convert_Treeview_to_Range ; place your cursor in this procedure and press F5 to run the procedure ; check Sheet5 for the result of the conversion.


Narayan