Nested dropdown of every 2 number just as tree

[tiong999]

Hello,

My appology whether i confused u of my title thread.
To the point i wonder how to nested my formula just like a tree of every 2 number nested.

Example:

1 ...................................... 1 character not stop until ended 2
2 ...................................... 1 character stop until ended 2
11 ...................................... 2 character not stop until ended 22
12 ...................................... 2 character not stop until ended 22
21 ...................................... 2 character not stop until ended 22
22...................................... 2 character stop until ended 22
111 ...................................... 3 character not stop until ended 222
112 ...................................... 3 character not stop until ended 222
121 ...................................... 3 character not stop until ended 222
122 ...................................... 3 character not stop until ended 222
211 ...................................... 3 character not stop until ended 222
212 ...................................... 3 character not stop until ended 222
221 ...................................... 3 character not stop until ended 222
222 ...................................... 3 character not stop until ended 222
And so on 1
and so on 2
and so on x1
and so on x2


Thanks for contributing our communities, im appreciated of any feed back.

Regards,
TIONG

 

[NARAYANK991]

Hi ,

Please upload a workbook with enough data in it.

Narayan

 

[tiong999]

Hi ,

Please upload a workbook with enough data in it.

Narayan

Hello,

Unfortunately i cant provide any data since this will no need any data, but suppose u look this tree chart within BOOK 1, u will notice the pattern i like to formulate.

Regards,
Tiong

 

[NARAYANK991]

Hi ,

This will require VBA , and even then it will work in versions 2010 and later only.

See the following thread :

https://www.mrexcel.com/forum/excel-questions/554224-using-vba-smart-art.html

Narayan

 

[bosco_yip]

Perhaps,

1] Here's a 3 levels dependent dropdown example of which prepared by Data Validation.

2] See attached file with formula indicated.

Regards
Bosco

 

[tiong999]

Hi ,

This will require VBA , and even then it will work in versions 2010 and later only.

See the following thread :

https://www.mrexcel.com/forum/excel-questions/554224-using-vba-smart-art.html

Narayan

Perhaps,

1] Here's a 3 levels dependent dropdown example of which prepared by Data Validation.

2] See attached file with formula indicated.

Regards
Bosco

Hello All,

Unfortunately that not help me either, since i not used excel 2010.
Nice for being contributed, i appreciated it.

Regards,
Tiong

 

[tiong999]

Hi ,

This will require VBA , and even then it will work in versions 2010 and later only.

See the following thread :

https://www.mrexcel.com/forum/excel-questions/554224-using-vba-smart-art.html

Narayan

Perhaps,

1] Here's a 3 levels dependent dropdown example of which prepared by Data Validation.

2] See attached file with formula indicated.

Regards
Bosco

Hello buddy,

I came across tutorial over internet and get idea how to generate it.
But, at very end i come the result generated by my trial and error formula get inflexible, thus give me error when the structure of row have change.

Please review me and hope any feed back from u to make it flexible.


Regards,
Tiong999

 

[John Jairo V]

Hi, to all!

Tiong999, you could try:
[C6] : =INDEX(C$5:C5,1+(ROWS(C$6:C6)-1)/2)&" "&INT(1+MOD(ROWS(C$6:C6)-1,2))

Blessings!

 

[tiong999]

Hi, to all!

Tiong999, you could try:
[C6] : =INDEX(C$5:C5,1+(ROWS(C$6:C6)-1)/2)&" "&INT(1+MOD(ROWS(C$6:C6)-1,2))

Blessings!

Thanks Buddy, it help me...but i give it little improvement as my intention.

[C6] : =INDEX(C$5:C5,ROUNDDOWN(1+(ROWS(C$6:C6)-1)/2,0))&" "&INT(1+MOD(ROWS(C$6:C6)-1,2))

Thanks and have a nice day.



Regards,
Tiong

 

[John Jairo V]

but i give it little improvement as my intention.

You don't need to round down in the row and column arguments of the INDEX function, just as I showed it in the proposal. INDEX takes the whole number without any rounding. Blessings!

 

[tiong999]

You don't need to round down in the row and column arguments of the INDEX function, just as I showed it in the proposal. INDEX takes the whole number without any rounding. Blessings!

Yes you got it right. Maybe i just fell asleep when copied it so i put the formula in cell C7 instead of C6.

Cheers buddy...have a great day today.

Regards,
Tiong