Cross Reference return Invalid Value for Text / Number starting with "0"

Did anyone ever encounter & solve this?

Thanks to the community I have setup a cross reference formula with INDEX (COLLECT(…),1) successfully across smartsheets. However, the formula return correct value when I reference normal text but FAIL while the reference value is "02" & it return invalid value instead.

I did notice Smartsheet automatically added "'" in front of "0" value but unsure if this will impact the formula or cross reference.

I copy the same formula from the 1st row to 2nd & 3rd row, only 1st row return INVALID VALUE:

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Yes. It is because of the apostrophe. That particular error from an INDEX/COLLECT is the same as "No Match" from an INDEX/MATCH. Is the apostrophe in front of the zero in your source data as well?

ZEIT

https://community.smartsheet.com/discussion/comment/446326#Comment_446326

Noted & thanks for responding. In fact, I didn't add the apostrophe manually but I input "02" directly as "Text／Number" and turn out I found that smartsheet automatically added apostrophe in front of it (if I try to click the cell for editing). Now both cells become " '02 " automatically - is there any workaround to overcome this?

Many thanks for your advice.

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.