+ Reply to Thread
Results 1 to 6 of 6

Assign values to names in a drop-down list?

  1. #1
    Barry L
    Guest

    Assign values to names in a drop-down list?

    Is it possible to assign values to names in a list, so that when you validate
    it as a drop-down list, you can select a name from the drop-down and it's
    corresponding value will be added to separate cell? Basically, I have survey
    questions which have five possible responses: Excellent - Very Good - Good -
    Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very
    Good and so on, so that when a response is selected from a drop-down, it's
    value appears in a separate cell (so that we can calculate a total and
    average score from the selections).

    Thanks
    B

  2. #2
    JulieD
    Guest

    Re: Assign values to names in a drop-down list?

    Hi Barry

    a couple of choices,

    1)
    if you only have 5 options you can use an IF function - assuming the drop
    down is in A1 the formula in B1 would be
    =IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
    Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))

    2)
    or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
    follows
    .........A.................B
    1....Grade.........Value
    2.....Excellent.......5
    3.....Very Good....4
    4.....Good.............3
    5......Fair...............2
    6.....Poor...............1

    and use a VLOOKUP function in cell B1
    =VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)

    - to trap for errors nest it in an IF(ISNA( function, e.g.
    =IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))

    3)
    use the following formula in B1
    =IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
    Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
    Good",4;"Good",3;"Fair",2;"Poor",1},2,0))


    Hope this helps
    Cheers
    JulieD


    "Barry L" <Barry L@discussions.microsoft.com> wrote in message
    news:84A74C29-3FBF-4728-A450-453AEAC76CAE@microsoft.com...
    > Is it possible to assign values to names in a list, so that when you
    > validate
    > it as a drop-down list, you can select a name from the drop-down and it's
    > corresponding value will be added to separate cell? Basically, I have
    > survey
    > questions which have five possible responses: Excellent - Very Good -
    > Good -
    > Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
    > Very
    > Good and so on, so that when a response is selected from a drop-down, it's
    > value appears in a separate cell (so that we can calculate a total and
    > average score from the selections).
    >
    > Thanks
    > B




  3. #3
    Barry L
    Guest

    Re: Assign values to names in a drop-down list?

    Great thanks Julie!

    Barry

    "JulieD" wrote:

    > Hi Barry
    >
    > a couple of choices,
    >
    > 1)
    > if you only have 5 options you can use an IF function - assuming the drop
    > down is in A1 the formula in B1 would be
    > =IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
    > Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))
    >
    > 2)
    > or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
    > follows
    > .........A.................B
    > 1....Grade.........Value
    > 2.....Excellent.......5
    > 3.....Very Good....4
    > 4.....Good.............3
    > 5......Fair...............2
    > 6.....Poor...............1
    >
    > and use a VLOOKUP function in cell B1
    > =VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
    >
    > - to trap for errors nest it in an IF(ISNA( function, e.g.
    > =IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))
    >
    > 3)
    > use the following formula in B1
    > =IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
    > Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
    > Good",4;"Good",3;"Fair",2;"Poor",1},2,0))
    >
    >
    > Hope this helps
    > Cheers
    > JulieD
    >
    >
    > "Barry L" <Barry L@discussions.microsoft.com> wrote in message
    > news:84A74C29-3FBF-4728-A450-453AEAC76CAE@microsoft.com...
    > > Is it possible to assign values to names in a list, so that when you
    > > validate
    > > it as a drop-down list, you can select a name from the drop-down and it's
    > > corresponding value will be added to separate cell? Basically, I have
    > > survey
    > > questions which have five possible responses: Excellent - Very Good -
    > > Good -
    > > Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
    > > Very
    > > Good and so on, so that when a response is selected from a drop-down, it's
    > > value appears in a separate cell (so that we can calculate a total and
    > > average score from the selections).
    > >
    > > Thanks
    > > B

    >
    >
    >


  4. #4
    JulieD
    Guest

    Re: Assign values to names in a drop-down list?

    you're welcome

    "Barry L" <BarryL@discussions.microsoft.com> wrote in message
    news:CED1DDEE-87C0-4920-9D5E-7A8FD2CAC497@microsoft.com...
    > Great thanks Julie!
    >
    > Barry
    >
    > "JulieD" wrote:
    >
    >> Hi Barry
    >>
    >> a couple of choices,
    >>
    >> 1)
    >> if you only have 5 options you can use an IF function - assuming the drop
    >> down is in A1 the formula in B1 would be
    >> =IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
    >> Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))
    >>
    >> 2)
    >> or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
    >> follows
    >> .........A.................B
    >> 1....Grade.........Value
    >> 2.....Excellent.......5
    >> 3.....Very Good....4
    >> 4.....Good.............3
    >> 5......Fair...............2
    >> 6.....Poor...............1
    >>
    >> and use a VLOOKUP function in cell B1
    >> =VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
    >>
    >> - to trap for errors nest it in an IF(ISNA( function, e.g.
    >> =IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))
    >>
    >> 3)
    >> use the following formula in B1
    >> =IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
    >> Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
    >> Good",4;"Good",3;"Fair",2;"Poor",1},2,0))
    >>
    >>
    >> Hope this helps
    >> Cheers
    >> JulieD
    >>
    >>
    >> "Barry L" <Barry L@discussions.microsoft.com> wrote in message
    >> news:84A74C29-3FBF-4728-A450-453AEAC76CAE@microsoft.com...
    >> > Is it possible to assign values to names in a list, so that when you
    >> > validate
    >> > it as a drop-down list, you can select a name from the drop-down and
    >> > it's
    >> > corresponding value will be added to separate cell? Basically, I have
    >> > survey
    >> > questions which have five possible responses: Excellent - Very Good -
    >> > Good -
    >> > Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
    >> > Very
    >> > Good and so on, so that when a response is selected from a drop-down,
    >> > it's
    >> > value appears in a separate cell (so that we can calculate a total and
    >> > average score from the selections).
    >> >
    >> > Thanks
    >> > B

    >>
    >>
    >>




  5. #5
    Registered User
    Join Date
    07-19-2010
    Location
    Liverpool
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Assign values to names in a drop-down list?

    Hi...I'm new to this! and I'm

    I'm running a Fantasy Formula 1 competition and have transfered the information over to Excel 2007 (Acer Laptop)

    The problem I have is to assign values to the drivers in a drop down cell?

    In cell A2:A27 are the names
    In cell B2:B27 are the values

    In cells G2,I2 and K2 are the drivers drop down list
    I require the values of each driver to appear in cells H2,J2 and L2
    each drive has a diffrent price tag.

    Many many thanks

  6. #6
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Assign values to names in a drop-down list?

    I have tried to follow these recommendations but I keep receiving a error message saying a parenthesis is missing somewhere. Please help.

+ 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