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

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"))

Find more posts tagged with

Accepted answers

Genevieve P.

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

All comments

Ella

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

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.

Hi @Joel Pierce

Let me know if this was the issue!

Cheers,

Genevieve

Joel Pierce

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.