Data Validation Question

**cmk18** · 10-07-2005, 09:48 AM

I have a survey where users score things on a linguistic scale (e.g. Very Satisfied, Satisfied, etc.) and I want to convert that to a numeric score, but still keep the Linguistic Value. I was hoping there was a way to have a cell with the linguistic value and the a cell with the numeric score, but have them both show up in a dv cell.

**swatsp0p** · 10-07-2005, 10:04 AM

I don't know what a "dv cell" is per your request. (...have them both show up in a dv cell)

However, if what you want is to have another cell return a numeric value based on the linguistic return of the validation choice, do this:

Create your list of linguistic choices in one column (say K), in the next column (J) enter the corresponding numeric value for each choice.

In the cell next to your validation cell, enter the following formula:

=VLOOKUP(A1,$K$1:$L$4,2,1)

where A1 is the validation cell, K1:L4 is the range of linguistic and numeric values. Of course, I prefer to name my table ranges, e.g. "Table1", and the formula would look like this:

=VLOOKUP(A1,Table1,2,1)

Does this work for you?

Zack Barresse · 10-07-2005, 12:05 PM

If you do not want to create a seperate table for your data to look it up
in, you can do this with one singular formula. This of course would limit
your future options would you like to make changes easily, of course.

=LOOKUP(A1,{"Satisfied","Unsatisfied","Very Satisfied","Very
Unsatisfied"},{2,3,1,4})

The largest problem I see with this is that there are two array vectors
here. The first one is the lookup vector and the latter one is the
return/result vector. The lookup vector *must* be in order sorted
ascendingly, this includes (numerically first) alphabetically; this is just
as Excel would sort a column ascendingly.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)

"cmk18" <cmk18.1wjc6a_1128693902.9734@excelforum-nospam.com> wrote in
message news:cmk18.1wjc6a_1128693902.9734@excelforum-nospam.com...
>
> I have a survey where users score things on a linguistic scale (e.g.
> Very Satisfied, Satisfied, etc.) and I want to convert that to a
> numeric score, but still keep the Linguistic Value. I was hoping there
> was a way to have a cell with the linguistic value and the a cell with
> the numeric score, but have them both show up in a dv cell.
>
>
> --
> cmk18
> ------------------------------------------------------------------------
> cmk18's Profile:
> http://www.excelforum.com/member.php...fo&userid=6047
> View this thread: http://www.excelforum.com/showthread...hreadid=474120
>

**cmk18** · 10-07-2005, 12:38 PM

That's not quite what I was looking for. I was hoping for something similar to what you can get with HTML where you display in the drop down both the numeric and linguistic value, but only the numeric gets recorded.

Debra Dalgleish · 10-07-2005, 03:05 PM

You could do this with programming. There's a sample workbook on my web
site that shows a product name and code in the Data Validation dropdown
list. After an item is selected, the cell shows only the product name.
You may be able to adapt this to your workbook.

On the following page:

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

Under the data validation heading, look for 'Data Validation "Columns"

cmk18 wrote:
> I have a survey where users score things on a linguistic scale (e.g.
> Very Satisfied, Satisfied, etc.) and I want to convert that to a
> numeric score, but still keep the Linguistic Value. I was hoping there
> was a way to have a cell with the linguistic value and the a cell with
> the numeric score, but have them both show up in a dv cell.
>
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html