Does the formula reference not recognize column names at a certain length or have issues with '-' ?

Options

I have some column names that are at or close to the max character limits and I've noticed that if I try typing in the formula with brackets, the references aren't recognized. I then would have to select the cell for each reference. Another work around I've found is to copy a valid formula and just replace the reference.

For example, below is a formula I'm using. I can type in [Max Duration Qs Form]@row and the formula recognizes the reference and color codes. But if I type in [Max Duration Q4 - Benefit Commencement Date]@row, the formula doesn't color code this section and if I finished the formula out, I get an #UNPARSABLE error.

=IF([Max Duration Qs Form]@row = 1, IF([Max Duration Q4 – Benefit Commencement Date]@row = "01/08/17", "Correct", "Incorrect"))

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Joel Pierce

    Formulas should recognize column names typed in with this symbol, however when I copied your example above I noticed that you're using an m-dash ( – ) versus an n-dash ( - ). Is it possible that when you type in the formula you're using the shorter dash-type, which is why the column name is not recognized?

    Let me know if this was the issue!

    Cheers,

    Genevieve

Answers

  • Ella
    Ella ✭✭✭✭
    Options

    Could it be that it is taking the - as a logical operator? Try eliminating the spaces before and after the - or rename that column?

  • Joel Pierce
    Options

    Possible, but I don't believe so since it functions as long as I don't type it in. The sheet has multiple questions, so Q1-Q15. If I copy the formula from a prior column (like for Q3) and paste in to the cell for Q4 and change the reference from Q3 to Q4, it works.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Joel Pierce

    Formulas should recognize column names typed in with this symbol, however when I copied your example above I noticed that you're using an m-dash ( – ) versus an n-dash ( - ). Is it possible that when you type in the formula you're using the shorter dash-type, which is why the column name is not recognized?

    Let me know if this was the issue!

    Cheers,

    Genevieve

  • Joel Pierce
    Options

    Thanks Genevieve - this does seem to work where I can type in alt+0150 for the dash in the formula. When creating the columns, I am generally just typing a standard hyphen/dash though and in some instances the hyphen worked and in others I had to use alt+0150 to use the m-dash. This was not intuitive to troubleshoot as it's difficult to see the difference as a user.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Joel, I'm glad we got to the bottom of it!

    Yes it can be difficult to see the difference between the two. Generally when I write formulas I click on the cell to populate the column name to make sure I'm copying all the formatting exactly how it's set up, as you noted in the original post.

    Thanks for following-up!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!