Currently I have a primary column in sheet 1 which is a value of 3 digits, all of which are numbers. These numbers are generated by the auto-number column type. I attempt to use this primary column as a match range to pull information with an index & match into sheet 2.
Performing a test using ISTEXT on sheet 1, all numbers in the primary column are indeed treated as text.
In sheet 2, for numbers up to 099, they are automatically considered text, but for anything greater than or equal to 100, they are considered as numbers. To correct this, I made another column and added a null string. I would expect the result to be that all of these values are now considered text and I can compare them easily to the primary column values in sheet 1. This is not the case. Even if the new column converts the numbers to text, when I write a formula in sheet 1 to compare the values, the values in sheet 2 are considered numbers even when referencing the column that was shown to be text. Understandably, this is detrimental to my process, as I am unable to find a way to guarantee that numbers greater than or equal to 100 are treated as text when referenced across sheets.
Furthermore, sheet 2 is populated by form entries. I thought if I include an apostrophe before the number when submitting the form, it may result in the number being recorded as text. Instead, it records it as text and includes the apostrophe at the beginning, rather than considering it as a number formatted as text. This means if you double click the cell, it actually has the number with two apostrophes before it.
Basically, it appears as if text vs number formats are not necessarily preserved when referenced cross-sheet, which I see as a big flaw. Is there any way to solve this, other than simply using an auto-number system with a non-numeric character? To me the fact that I cannot do it the current way, assuming I am not missing something, shows a bug/flaw in the software.
Thank you,