How can I display text and #s in a drop-down list, but insert only

[JHartle]

I am creating a survey and each cell has a drop-down list that displays the
Likert scale from 1-5. I would like the drop down list to display the Likert
description in the drop-down, for example: 1-Strongly Disagree, 2-Disagree,
3-Neutral, and so on up to 5. But when the user selects one of the options, I
would like only the number to be inserted into the field because we are
averaging the scores.

 

[Chip Pearson]

If you know the text value, you can get it from the input list. E.g,

=OFFSET(FirstCell,LinkedCell-1,0,1,1)

where FirstCell is the first cell of the input list and LinkedCell is
the cell to which the combobox is linked (in the Properties window).

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JHartle]

I tried to do this by entering =OFFSET(a137,b3-1,2,3,4,5) but it didn't work.
Unfortunately I think i am too much of a novice to understand your advice. I
don't understand the linked cell. My likert scale is on a separate worksheet.
Should I create another named list on that worksheet?
There are five options for fixed text values for each cell in the worksheet
(all the cells in the worksheet have the same likert scale attached). I want
the person to select, for example, 1-Strongly Disagree and then I want the
cell to have the value 1.
Is there any way of telling the spreadsheet that 1-Strongly Disagree=1?

 

[Fred Smith]

Now we know what you tried. Now tell us what happened when you tried Chip's
formula. Don't change it with "2,3,4,5". Enter it the way he recommended.

Regards,
Fred.

 

[JHartle]

I did as you said and it said that my formula had a circular reference and
then that my formula had an error. this was the formula i entered:
=OFFSET(a137,b3-1,0,1,1)