#### Data Validation Question #3

```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?action=getinfo&userid=6047

```
 0
10/7/2005 1:48:57 PM
excel 39879 articles. 2 followers.

4 Replies
517 Views

Similar Articles

[PageSpeed] 46

```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?

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101

```
 0
10/7/2005 2:04:41 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?action=getinfo&userid=6047
>

```
 0
firefytr (112)
10/7/2005 3:55:30 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.

--
cmk18
------------------------------------------------------------------------
cmk18's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6047

```
 0
10/7/2005 4:38:47 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

```
 0
dsd1 (5911)
10/7/2005 6:47:38 PM

Similar Artilces:

Lost Active Directory for Microsoft CRM 3.0
Hi, We recently had a server failure on our Domain Controller which was unrecoverable and our backups for active directory failed to restore. The CRM database and application are on a seperate server so is fully intact. But we have lost the AD, which means no access. My question is what is the best method if any to recover the CRM system? I was hoping in version 3.0 this is possible as i know 1.2 was a nightmare due to the security descriptors. Am i able to reinstall and attach to existing database and apply CRM customization? Thanks, Pete Pete, You're in luck, this shouldn'...

[ANN] Office 11.3.8 update
Hi All, A new security update just came out. I suspect it requires 11.3.7 to install. Anyway, time to run the Microsoft Updater or to grab it from <http://www.microsoft.com/mac/downloads.aspx> Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.support.microsoft.com MVPs are not MS employees - Les MVP ne travaillent pas pour MS Remove "NoSpam" to e-mail me - Retirez "NoSpam" pour m'�crire I just let the Microsoft AutoUpdate do its thing. The only Office...

conversion #3
I am trying to upgrade from Outlook 2000 to Outlook 2003 on a new computer. When I attempt to export the data in my email files (inbox, sent,etc.)I do not have the "received" information exported - when mapping the fields I am unable to map several of the email fields; therefore, they are not exported. I have been unable to find the normal email fields to use in my mapping. How do I map normal email fields in an export operation? If I do not export this information my new computer has no idea when I received my old emails/ In the old days, the easiest way to keep all da...

BackUp #3
how i can take backup of all my company emailboxes and addresses? ...

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

3 Levels in Extended Pricing
Is it possible in Extended Pricing to have 3 levels of discounts. I assume yes because the max level in price trace is 3. I set up my Base Price Book to be all my List Prices. I then set up another price book and set it to be 50% of Base (Dealer Pricing) and then I want another price book to be "10% off of Dealer" I would then assign customers to this special price sheet/book. So doing a price trace I would have: List: \$100 Dealer Original \$100 Percent off 50% Unit Price \$50 Special Original: \$50 Percent off 10% Unit Price \$45 Under my "Price Sheet Detail Maintenance...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Custom reports #3
Hi, I want to pull a report on how many customers I am getting every hour of the day. Does anyone out there know how to do that? I can try writing a query if someone can tell me which table I can find this information in (timestamp, transaction etc). Any help is appreciated. Regards JD This info is given at the end of the Z report. If you want to use it in a query open the Z report recipt file using the Notepad and see the fields that are used... Report.Hour.HourSales etc... Hope this helps... Carlos "JD" wrote: > Hi, > > I want to pull a report on how many cust...

ANN: Optimizing the Performance of Microsoft Dynamics CRM 3.0 white paper
Hi, the White Paper "Optimizing the Performance of Microsoft Dynamics CRM 3=2E0" is now available: http://go.microsoft.com/fwlink/?LinkID=3D80916=20 Regards Nicolas F=FChrs ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

Outlook Question
What causes url links not work? I'm experiencing problems with it, and cannot get them to show the URL Links. Clicking the Links doesn't help. ...

cannot send e-mail #3
I am getting the following message: message from Outlook 2000, you may receive the following error message: 0X800CCC78 error: The message could not be sent because the server rejected the sender's e-mail address It gradually has disabled all of my 3 accounts on Outlook. I can send, but not receive. I think there is some software corruption or a virus. Deleting and re-creating the account does not work Mario D'Avirro <mariod@mdainsurance.com> wrote: > I am getting the following message: > > message from Outlook 2000, you may receive the following > error mes...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Report: Cell #1, Cell #2, Cell #3, Cell #4
I am stuck again and would love som help :( I would like to repeat all words found inside ~25 cells, separated only by ", ", ignoring empty cells. Data: A1: [Apple ] A2: [Orange] A3: [Banana] A4: [Tomato] A5: [Syrup ] A6: [ ] A7: [ ] A8: [ ] The result should be something like: [Apple, Orange, Banana, Tomato, Syrup] -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945 View this thread: http://www.excelforum.com/showthread.php?thre...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...