Formula to concatenate multiple "tab" characters doens't work as expected
I created a formula to try to create an indented structure for reports by concatenating multiple "tab' characters based on the indent level. However, the formula doesn't work as expected. Instead of ending up with multiple "tab" characters, the output is either an empty string (when lvl=0 or lvl=1) or it's the number zero (for all other lvl values). I think the (rather odd) choice of Smartsheet to use of the "plus" sign for addition and for concatenation is backfiring, as I can only assume by the numeric output that somehow Smarsheet is trying to numerically add my non-numeric characters, which is resulting in the return value of 0. Very odd. Any suggestions?
=IF([WBS_Lvl]@row = 0, "") + IF([WBS_Lvl]@row = 1, CHAR(9)) + IF([WBS_Lvl]@row = 2, CHAR(9)) + IF([WBS_Lvl]@row = 3, CHAR(9)) + IF([WBS_Lvl]@row = 4, CHAR(9)) + IF([WBS_Lvl]@row = 5, CHAR(9)) + IF([WBS_Lvl]@row = 6, CHAR(9)) + IF([WBS_Lvl]@row = 7, CHAR(9)) + IF([WBS_Lvl]@row = 8, CHAR(9)) + IF([WBS_Lvl]@row = 9, CHAR(9)) + IF([WBS_Lvl]@row = 10, CHAR(9)) + IF([WBS_Lvl]@row = 11, CHAR(9)) + IF([WBS_Lvl]@row = 12, CHAR(9)) + IF([WBS_Lvl]@row = 13, CHAR(9)) + IF([WBS_Lvl]@row = 14, CHAR(9)) + IF([WBS_Lvl]@row = 15, CHAR(9))
EDITS/UPDATES:
1) Tried replacing CHAR(9) with multiple spaces. Same result as the original formula - either a zero-length string or number zero as the output.
2) Tried converting this to a nested ifs formula - that doesn't work either. Just get a zero-length string. After some searching, it seems Smartsheet doesn't allow leading spaces... lame.
3) Tried replacing the spaces with periods. STILL does not work. I get one set of periods for the first and second levels, and everything else just a zero followed by one set of periods. I really don't understand this behavior.
Best Answer
-
Finally found a working workaround to the many ways that Smartsheet apparently tries to discourage manual indenting.
The following formula works and gives me the expected results.
=IF([WBS_Lvl]@row = 2, "....", IF([WBS_Lvl]@row = 3, "........", IF([WBS_Lvl]@row = 4, "............", IF([WBS_Lvl]@row = 5, "................", IF([WBS_Lvl]@row = 6, "....................", IF([WBS_Lvl]@row = 7, "........................"))))))
Answers
-
Finally found a working workaround to the many ways that Smartsheet apparently tries to discourage manual indenting.
The following formula works and gives me the expected results.
=IF([WBS_Lvl]@row = 2, "....", IF([WBS_Lvl]@row = 3, "........", IF([WBS_Lvl]@row = 4, "............", IF([WBS_Lvl]@row = 5, "................", IF([WBS_Lvl]@row = 6, "....................", IF([WBS_Lvl]@row = 7, "........................"))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!