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

ZEIT

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:

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.