Data Validation Question

  • Thread starter Thread starter cmk18
  • Start date Start date
C

cmk18

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.
 
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?
 
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
 
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.
 
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"
 
Back
Top