Results 1 to 9 of 9

Thread: NEED : Multiple conditions, drop down and return a numeric value - HELP!

  1. #1

    Exclamation NEED : Multiple conditions, drop down and return a numeric value - HELP!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Excel-perts:
    I am desperately trying to do the following and need advice:
    In a cell, I need to
    1. Click to reveal a drop down list of 5 options
    1 - No Experience
    2 - Novice
    3 - Average Knowledge
    4 - Experienced Application
    5 - Expert Installer
    2. Then, in choosing one of the 5 options, it needs to return a numerical value 1-5, rather than the entire option text;
    3. Then, when returning the appropriate value, I want the cell to fill with a different color based on a simple chart:
    1 = red fill
    2 = yellow fill
    3 = green fill
    4 = blue fill
    5 = purple fill

    Does anyone know how I code this multi-function formula?
    Many thanks!!!!
    Lynne

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello
    Take a look at my attachment. Is that what your looking for?
    Attached Files Attached Files

  3. #3
    Thanks Hercules, but the attachment didn't have any coding or format. Can you resend?

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by LynneB View Post
    Thanks Hercules, but the attachment didn't have any coding or format. Can you resend?
    Helllo Lynne
    It seems to work OK to me. If you click the drop down to cell A1 and select one of the text entries, cell A1 should change to the appropriate colour.
    Let me know if that doesn't work.

  5. #5
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi Lynne,
    pl find attachment. is it help you ?
    Attached Files Attached Files

  6. #6

    Post

    Hi LynneB,

    Have a look at this file. The drop down is in cell A1. As soon as you choose the option, it is converted into the corresponding number (using custom number formatting). And then Conditional Formatting does the color fill.

    Custom and Conditional Formatting.xlsx
    Cheers
    Sumit Bansal
    Excel MVP (2014-15)
    Founder, Trump Excel

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by sumitbansal View Post
    Hi LynneB,

    Have a look at this file. The drop down is in cell A1. As soon as you choose the option, it is converted into the corresponding number (using custom number formatting). And then Conditional Formatting does the color fill.

    Custom and Conditional Formatting.xlsx
    Sumit
    I think lynne has found an answer somewhere else. Can you explain how you get the numbers into A1 please ? I can see the custom format text entries, but not how the numbers are linked to them?

    Thanks
    Last edited by Hercules1946; 2014-12-18 at 03:08 PM.

  8. #8

    Arrow

    Hello Hercules,

    Try this:

    - Type 0 in a cell
    - Press Control + 1 and select custom format
    - In custom format field, type "No Experience"

    Now this cell will have the value 0, but show "No Experience"

    Now if you use this cell in data validation, it will show No Experience, but will hold the value 0.

    I have an article on my website (since I am not allowed to paste the url link till I make 5 posts, pardon my weird way of explaining the url)

    Go to TrumpExcel . Com and search for the word disguise. The first article that you get will show a detail way of doing this.
    Cheers
    Sumit Bansal
    Excel MVP (2014-15)
    Founder, Trump Excel

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Sumit
    Thank you. I wasn't aware of that.

Tags for this Thread

Posting Permissions

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