Data Validation - Multiple columns in drop down?

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!

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 wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html

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

Debra Dalgleish wrote:
> 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 wrote:
> > 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
> Contextures
> http://www.contextures.com/tiptech.html

Hi,

I've looked at DV004 and DV005, both of which work very nicely, but...

I've set it up to display a description and use the list/vba to enter the corresponding code, e.g. the user selects 'Finance' from the drop-down list and the code 'Fin' is entered into the cell.

Is there anyway of doing this so that a user can optionally enter the code (Fin) directly, rather than selecting it from the Drop-down list? Even better with the same capitalisation, e.g. 'Fin' not 'fin'?

As things stand, trying to do this produces an error message.

Many thanks.

Peter