Data Validation - Multiple columns in drop down?

[bluegrassstateworker]

In Access, I can have a drop down list based on another table and can
show several fields within that dropdown list but the value selected is
based on one field. For Example, (in Access) a drop down list may
show: 721 SERVICE DEPARTMENT, but only the value 721 may be input into
the value selected.

Is there a way this can be done with the data validation within Excel?
I have a spreadsheet given to users as a type of electronic form that
is sent back to me for consolidation. The users need more explanation
than I do; I already know that 721 is the SERVICE DEPARTMENT.

I did have two separate columns: one for 721 and another for SERVICE
DEPARTMENT but ended up concatenating the value to another column for
use in my data validation list. This clutters the spreadsheet with
data I dont need. I will eventually be exporting this information into
an Access database and want to keep the field sizes as small as
possible since a separate table can be linked to define that 721 will
equal "SERVICE DEPARTMENT"

Any Ideas? Thanks!

 

[Debra Dalgleish]

You can do this with data validation and programming. There's a sample
file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0005 - Data Validation "Columns"'

 

[bluegrassstateworker]

That did exactly what I was wanting it to. Thanks so much!