Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop Down Menu (Office 2000)

    Problem:

    In cell A1 i have a drop down menu, from data validation, and in the drop down menu i have three items: MPOA, MPOA/MFPS, and MP-12. As an FYI - MPOA = 0, MPOA/MFPS = 226, and MP-12 = 3,383.

    When i select any one of these items from the drop down menu i would like the corresponding number to appear in cell B1. Example, if i pick MP-12 in cell A1 from the menu, the number 3,383 will automatically "pop-up" in B1, if i pick MPOA/MFPS in cell A2 then 226 "pops-up" in cell B1 and so on.

    I have been trying to use IF statements but when i nest more than one if statement the cell rejects the formula. Is there another way to accomplish this? Any help/suggestions will be much appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    I think this achieves what you want - presumably the validation is done from a list - match the codes in an adjacent column with the numeric partners and use a vlookup. but the spreadsheet explains it better.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    When you say match the codes do you mean using "Match" under the formula toolbar?

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    No. in your validation list, in column a list out the part numbers - or what ever they are. Then, in column b, enter the corresponding numerical value - on the same row as the part number resides. Absolutely nothing to do with the function match. A bad choice of terminology on my part, for which I apologise. As I said, have a look at the spreadsheet I attached. It should explain my approach.

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    OK, i have the formula, which i was close i did not have false in the range lockup. But i am still having problems with the match. This is the first time i worked with formulas that invlolved so many other variables.

    Thanks, Cosmo

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    cosmo,

    I'm not sure what your problem is. If you have the vlookup working you should be home dry. Could you elaborate on what is not working for you? Maybe you could attach the spreadsheet you're working on?

  7. #7
    New Lounger
    Join Date
    Oct 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    Please see attached.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    in your vlookup,change 10 to 2 - this is the column index - you want the second column in your reference. that should sort it for you.

  9. #9
    New Lounger
    Join Date
    Oct 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Menu (Office 2000)

    Brooke,

    You have been a big help, thank you for your time and patients.

    Cosmo

Posting Permissions

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