Forum Discussion
Manchester05
Oct 11, 2023Copper Contributor
tree hierarachy chart
I want to create an hierarchy chart, comparable to the 1752 version below. I have a growing collection of data points which can each categorised into an hierarchy position and level. Is there a too...
- Oct 12, 2023You'd need VBA for that. I found a discussion here: https://www.mrexcel.com/board/threads/using-vba-for-smart-art.554224/
But if you're OK with VBA, perhaps you need a more sophisticated implementation, like a treeview?
https://jkp-ads.com/articles/treeview.asp
PeterBartholomew1
Oct 12, 2023Silver Contributor
The conditional formatting is not the greatest but the basic tree is in place
[I ignored the request for multiple rows because there were no section headings and there was a clash with subsequent numbering]
= LET(
level, 1 + QUOTIENT(LEN(section),2),
prior, DROP(VSTACK(0, level),-1),
row, SCAN(1, level>prior, LAMBDA(acc,s, acc+1-s)),
location, row & "," & level,
tree, MAKEARRAY(9,7, LAMBDA(r,lv, XLOOKUP(r&","&lv, location, LEFT(heading,30),""))),
tree
)
though in use it would be
= Treeλ(section, heading)