Data Validation - Want to Accept Numbers in Multiple Formats

Is there a way to set up a cell with a data validation list that does not discriminate based on format? So if the number 42 was in the validation list, the cell would accept either the number 42 or the character 42?

I'm setting up a data entry worksheet, and one of my fields is a unique identifier number. The number is usually input as a text field to maintain ocasional leading zeroes.

The issue is, I want the users to be able to either paste in the ID from another source (most likely formatted as text), type the number into the cell (which defaults to a number format), or pick the correct ID from a dropdown. And I don't want to accept any other value in that field.

I know that I could just double the size of the data validation list to include the full set as numbers and the full set as characters, but then the dropdown list would be cluttered with all those duplicates. I'm hoping there's a better way...

Thanks in advance for any help!

Try adding VALUE(cell) instead of just cell reference. Excel will then use the numeric form of a text-formatted number.

Hope that helps.

-Z

Always happy to help. 8 )

-Z

I believe I've found my solution.

If I leave my data validation list in text format, and then change the format of the cells where the data will be entered to "zip codes" (format cells -> special -> zip codes) that seems to do the trick. Now when a user enters a number manually, it stays in zip (which is text) format instead of automatically converting to number format which the data validation doesn't recognize.