Hi,
I've come across this problem twice this week already and have a feeling it will crop up again. Try as i might, I cant seem to get around it.
Basically I've got a VLOOKUP function that references one Text cell and looks for a matching Text cell on another sheet. Simple. Both cells are formatted as Text, so the VLOOKUP should work perfectly... but....
One of these Text cells is flagged with the 'green triangle' error check stating "Number Stored As Text". This is true - it is a 'number' but we want it stored as text so we ignore the error. Unfortunately, the cell it references is a text cell without the "Number Stored as Text" error and the VLOOKUP fails because of this.
The VLOOKUP does work if both cells have the "Number Stored as Text" error, but wont work if only one has the error - even though they are both text!
The only way to get the error flag to show up on the text cells is to double-click the cell and hit enter - but this is not practical for the hundreds / thousands of rows.
The alternative is to remove the error flag from the text cells but the only way to do this is to convert the cells to numbers, which we do not want to do. Seeing as the cells are already formatted as Text, I cannot see a way to get rid of this error and keep the cells as Text.
I hope the above makes sense - I can clarify further if need be. Any help would be appreciated - I have searched on the web but havent found anything pertaining to this particular issue.
Cheers,
Ruby
I've come across this problem twice this week already and have a feeling it will crop up again. Try as i might, I cant seem to get around it.
Basically I've got a VLOOKUP function that references one Text cell and looks for a matching Text cell on another sheet. Simple. Both cells are formatted as Text, so the VLOOKUP should work perfectly... but....
One of these Text cells is flagged with the 'green triangle' error check stating "Number Stored As Text". This is true - it is a 'number' but we want it stored as text so we ignore the error. Unfortunately, the cell it references is a text cell without the "Number Stored as Text" error and the VLOOKUP fails because of this.
The VLOOKUP does work if both cells have the "Number Stored as Text" error, but wont work if only one has the error - even though they are both text!
The only way to get the error flag to show up on the text cells is to double-click the cell and hit enter - but this is not practical for the hundreds / thousands of rows.
The alternative is to remove the error flag from the text cells but the only way to do this is to convert the cells to numbers, which we do not want to do. Seeing as the cells are already formatted as Text, I cannot see a way to get rid of this error and keep the cells as Text.
I hope the above makes sense - I can clarify further if need be. Any help would be appreciated - I have searched on the web but havent found anything pertaining to this particular issue.
Cheers,
Ruby
