+ Reply to Thread
Results 1 to 5 of 5

Converting A Word/Value To A Number

  1. #1
    Registered User
    Join Date
    11-07-2008
    Location
    London
    Posts
    2

    Converting A Word/Value To A Number

    Afternoon, i need help. Apologies if this may be in the wrong forum, its my first time on here.

    I am creating a review sheet/skill matrix using excel and need to be able to convert a particular selection to a number in the adjacent cell to work out the users efficiency (long story that i wont bore you with)

    So basically i have a question/statement in cell A and a drop down answer menu in cell B containing up to 5 possible answers (very poor, poor, ok, good, very good). In cell C i would like to insert a formula that allocates a number dependant on the selection in cell B. So if the user selects very poor in cell B cell C states 1, if cell B has poor selected then cell C states 2 etc etc

    Anyone have any ideas if this can be done and how i do it.

    Thanks
    Shez
    Last edited by VBA Noob; 11-07-2008 at 01:48 PM. Reason: Marked as solved

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Please Login or Register  to view this content.
    Easier would be to put your chart in a table so that a simple VLOOKUP can find it. Put it in AA1:5 and the answers in AB:5 and this code would work, much cleaner:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Are you using a Data Validation Dropdown list or Control Toolbar List or Forms List?

    If it's DV and you're pulling from a list in the spreadsheet (e.g. E1:E6 =Very Poor, Poor, etc), Then in the cell you want a number in, you can use (with your dropdown in B1)
    Please Login or Register  to view this content.
    With the Forms Listbox, you can output a number directly from the list (Right click on your Listbox and format control). Did either of these help you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Converting A Word/Value To A Number

    Perhaps one of these approaches....
    A1: (contains a phrase from the list....or is blank)

    Please Login or Register  to view this content.
    Or....if you don't want the list in a vulnerable spot in the workbook...
    From the Excel Main Menu:
    Please Login or Register  to view this content.
    Then
    B1: =MATCH(A1&"",RateList,0)-1
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Registered User
    Join Date
    11-07-2008
    Location
    London
    Posts
    2
    Quote Originally Posted by ChemistB View Post
    Are you using a Data Validation Dropdown list or Control Toolbar List or Forms List?

    If it's DV and you're pulling from a list in the spreadsheet (e.g. E1:E6 =Very Poor, Poor, etc), Then in the cell you want a number in, you can use (with your dropdown in B1)
    Please Login or Register  to view this content.


    With the Forms Listbox, you can output a number directly from the list (Right click on your Listbox and format control). Did either of these help you?
    Excellent.... this seems to be working

    Thanks very much for all your responses guys

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1