Formula for new column to get a hierarchy view in report
Morning,
I am working on code to get some type of hyerarchy view in a report
I added the below code and it is just coming up as blank
I spread the code out a bit so you could see what I was trying to do, where did i go wrong?
=IF(CONTAINS(0, [Task Level]@row), [Task Name]@row,
IF(CONTAINS(1, [Task Level]@row), "" + [Task Name]@row,
IF(CONTAINS(2, [Task Level]@row), "" + [Task Name]@row,
IF(CONTAINS(3, [Task Level]@row), "" + [Task Name]@row,
IF(CONTAINS(4, [Task Level]@row), "" + [Task Name]@row,
IF(CONTAINS(5, [Task Level]@row), "" + [Task Name]@row,
IF(CONTAINS(6, [Task Level]@row), "" + [Task Name]@row))))))))
Best Answer

Instead of the CONTAINS function, try making it equal to the number:
=IF([Task level]@row = 0, [Task name]@row, ....................
You can also save yourself some keystrokes by using the nested IF to generate just the dashes and then add the task name to the end of the nested IF.
=IF(.....IF(.....IF(.....))) + [Task Name]@row
Answers

Instead of the CONTAINS function, try making it equal to the number:
=IF([Task level]@row = 0, [Task name]@row, ....................
You can also save yourself some keystrokes by using the nested IF to generate just the dashes and then add the task name to the end of the nested IF.
=IF(.....IF(.....IF(.....))) + [Task Name]@row

And this actually inspired me to try something out that will simplify everything for you.
=LEFT("", [Task Level]@row * 2) + [Task Name]@row
The above should work to apply two hyphens for every level. So level zero would have none, level 1 would have 2, level 2 would have 4, so on and so forth all the way up through 12 for level six (which is what your formula looks like it is currently doing).

Thank you that worked with code completed like this:
=IF([Task Level]@row = 0, [Task Name]@row, IF([Task Level]@row = 1, "" + [Task Name]@row, IF([Task Level]@row = 2, "" + [Task Name]@row, IF([Task Level]@row = 3, "" + [Task Name]@row, IF([Task Level]@row = 4, "" + [Task Name]@row, IF([Task Level]@row = 5, "" + [Task Name]@row, IF([Task Level]@row = 6, "" + [Task Name]@row)))))))

Happy to help. 👍️
You can also save yourself a bit of typing in the future using the LEFT formula I provided above.

thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!