Hello...

I've got a workbook containing two sheets. The first sheet is intended to
be a template that a user fills out with various information pertaining to
part orders. Each row corresponds to a machine part. The goal is to have
the user fill out the workbook and send it back to us, and then we save it in
a pre-defined tab delimited format and our software uploads that specially
formatted file and inputs it into a database.

The data-entry spreadsheet needs to be a special format, as I've already
mentioned. I am restricting what the user can enter in certain columns using
a list validator. For example, the "Unit of Measure" column can only be
certain values.

The problem is, the meaningful text descriptions for unit of measure that a
person would understand as they are filling out the spreadsheet (such as
"each", or "barrel", or "case") are not acceptable input to the upload
software. Each unit of measure has a special 'code' corresponding to it...
for example "each" might have a code of "ea" and "barrel" might be "bbl".

I have the text descriptions and codes mapped in the second worksheet of the
workbook, and it's easy to make a column in the data entry workbook get
populated with the coded value when the user picks a "full text description"
from the dropdown -- HOWEVER, this messes up the format of the input file
since we now have an additional column.

Is there a way to make the drop down list display one set of values (the
full text descriptions) but put a second set of values (the code values) into
the cell when the user selects one? This is similar to how an HTML drop down
list control works. Something like that would be ideal.

Again, the goal is for a simple spreadsheet the user fills out, then saves
as tab delimited, without having to do any data manipulation like deleting
extra columns and whatever else.

Thanks!

John