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:
Answers
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!