excel dropdown list - value different top option name

[tyutghf]

I need to create an excel document that some data inputters will use.

We have questions such as

How satisfied are you with tek-tips?
- very satisfied
- fairly satisfied
- neither nor
- fairly disatisfied
- very disatisfied

The data may at some point go into a statistical package, is it possible to create a dropdown in excel that will have the names (labels) above but when they select it numbers are saved.

i.e. very satisied is 1, failry satisfied is 2

The easy answer is to just have the inputters enter 1-5 depending on the satisfaction but believe me this doesn`t happen and they enter all sorts of numbers. I need it to be as easy as them as possible but store the data in a format that can inserted into something such as SPSS.

Is this possible?

I can easily (using data>validation) to create the dropdown with the friendly names but the values stored are also the friendly names. I need them to select faily satisfied and it store as a 2.

Thanks

 

[SkipVought]

hi,

Use a FORMS Combobox.

The LinkedCell property returns the index value (1,2,3 etc)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tyutghf]

Is it possible to assign combo boxes to cells as entire columns need to have the dropdown options

i.e. all rows in column b need to be selected via the dropdown.

initially there are about 5000 rows to be inputted with about 10 a day ongoing.

 

[SkipVought]

5000!!!

Yikes! I would not want to scroll down and search!

Please describe the PROCESS in which the drow down is intended to be used.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

or are you saying that there are 5000 rows to which options 1-5 are to be assigned?

If so, use a Data > Validation LIST in an adjacent column.

Enter your LIST on a separate sheet and give it a Range Name.

Reference the Range Name as the source for the Data > Validation LIST

Then the 1 - 5 values could be 'calculated' after all 5000 rows are assigned, using the MATCH function.

SIMPLE!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The only problem I would see with your drop down approch is that each reply takes TWO mouse clicks: one to open the drop down and a second to make a selection.

This kind of survey is usually done with an Exclusive Option control (the Option Button): ONE CLICK.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}