Formula to concatenate multiple "tab" characters doens't work as expected

Soeder1
Soeder1 ✭✭
edited 03/03/23 in Formulas and Functions

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.


Tags:

Best Answer

  • Soeder1
    Soeder1 ✭✭
    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

  • Soeder1
    Soeder1 ✭✭
    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, "........................"))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!