Getting “Unparseable” error on nested IF/AND/OR formula with cross-sheet references

Hi everyone,

I’m rebuilding an Operational Status formula that references another sheet for quarter-end deadlines. The formula works logically, but Smartsheet keeps returning #UNPARSEABLE.

Here’s the current version (I’ve confirmed all range names and column types are correct):

=IF(
AND(
OR([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", [Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes"),
IF([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", ISDATE([CQ: Completed Date]@row ), TRUE),
IF([Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes", ISDATE([PQ Date Completed]@row ), TRUE)
),
"Completed",
IF(
AND(
ISBLANK([Tax COE Date Activation Issues Resolved]@row ),
ISBLANK([Customer Completed Date]@row ),
OR([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", [Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes"),
IF([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", ISBLANK([CQ: Completed Date]@row ), TRUE),
IF([Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes", ISBLANK([PQ Date Completed]@row ), TRUE)
),
IF(
AND(
ISDATE(INDEX({PS GD Deadlines Range 2}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0))),
TODAY() > INDEX({PS GD Deadlines Range 2}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0))
),
"Not Started (Past Due)",
"Not Started"
),
IF(
AND(
OR(ISDATE([Tax COE Date Activation Issues Resolved]@row ), ISDATE([Customer Completed Date]@row )),
OR(
AND([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", ISBLANK([CQ: Completed Date]@row )),
AND([Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes", ISBLANK([PQ Date Completed]@row ))
),
ISDATE(INDEX({PS GD Deadlines Range 4}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0))),
ISDATE(INDEX({PS GD Deadlines Range 2}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0))),
TODAY() >= INDEX({PS GD Deadlines Range 4}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0)),
TODAY() <= INDEX({PS GD Deadlines Range 2}, MATCH([Reporting Quarter]@row , {PS GD Deadlines Range 3}, 0))
),
"In Progress (Restriction Period)",
IF(
AND(
OR(ISDATE([Tax COE Date Activation Issues Resolved]@row ), ISDATE([Customer Completed Date]@row )),
OR(
AND([Step 1: CQ LAUNCH Required (Yes or No)]@row = "Yes", ISBLANK([CQ: Completed Date]@row )),
AND([Step 1: PQ LAUNCH required (Yes or No)]@row = "Yes", ISBLANK([PQ Date Completed]@row ))
)
),
"In Progress",
"Not Started"
)
)
)
)

Has anyone seen this happen with large nested formulas and cross-sheet INDEX/MATCH?
Any syntax or length limits I should be aware of?

-TIA!

Find more posts tagged with

Formulas

#UNPARSEABLE

Cross Sheet Reference

index/match

Accepted answers

All comments

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it? Can you describe what it is you want your formula to do?

Jgorsich

There is a 4000 character limit but you are pretty shy of that at 2183. Checking through your formulas doesn't reveal anything obviously wrong that would trigger this specific error. That being the case, one of the more likely failure causes is a spelling error in one of your references - either an extra or missing space or a missing colon. I noticed you tend to use colons EXCEPT in your [PQ Date Completed] column name (vs your [CQ: Completed Date] column name); if that should actually be [PQ Date Completed] that would cause your issue. I suspect looking for that type of problem is part of why Paul asked for the picture - seeing it in the edit state gives lots of hints due to the color coding (especially, if one of your references isn't color coded it isn't recognized).

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.