+ Reply to Thread
Results 1 to 8 of 8

Drop down menu and product - thank you

  1. #1
    Registered User
    Join Date
    04-07-2009
    Location
    Romania
    MS-Off Ver
    Office XP
    Posts
    4

    Drop down menu and product - thank you

    Hi guys,

    This is my first post here so I hope I will do it ok.

    I have a problem that seems to be a little over my head...

    I'm trying to make a table that looks like this:

    X Y Z Q Rezult


    I need X to be a drop down menu with 3 possibilities: A, B, C

    Now if X is A the result looks like this:

    Result=Y*1.5*Z*Q*20

    if X is B the result looks like this:

    Result=Y*1*Z*Q*10

    if X is C the result looks like this:

    Result=Y*0.5*Z*Q*30

    I hope it's possible ...


    PS. excuse my english
    Last edited by Xample; 04-08-2009 at 09:31 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Drop down menu and product

    If we assume X is column A, Y is B etc such that Result is E

    E1: =B1*LOOKUP(A1,{"A","B","C"},{1.5,1,.5})*C1*D1*LOOKUP(A1,{"A","B","C"},{20,10,30})

    which could be shortened I think to:

    E1: =B1*C1*D1*LOOKUP(A1,{"A","B","C"},{30,10,15})

    Does that work for you ?

  3. #3
    Registered User
    Join Date
    04-07-2009
    Location
    Romania
    MS-Off Ver
    Office XP
    Posts
    4

    Re: Drop down menu and product

    your formula looks like what I want but for some reason there is an error...

    I attached the file to make sure I did everything right
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Drop down menu and product

    The result I get with

    E1: =B1*C1*D1*LOOKUP(A1,{"A","B","C"},{30,10,15})

    is 2,700,000

    The above is based on UK regional settings - I suspect you may need to alter the delimiters per your own settings (ie use of ; etc...)

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Drop down menu and product

    Disregard that Xample - point holds re: regionals BUT the criteria is now "one" "two" "three" as opposed to "A","B","C" ... Lookup generally requires the values be sorted in Ascending order... in this instance rather than re-order such that they appear out of sequence (eg "one","three","two") I would switch approach from LOOKUP to INDEX/MATCH

    =B1*C1*D1*INDEX({30,10,15},MATCH(A1,{"one","two","three"},0))

  6. #6
    Registered User
    Join Date
    04-07-2009
    Location
    Romania
    MS-Off Ver
    Office XP
    Posts
    4

    Re: Drop down menu and product

    still not working

    I attached the print screen with the error

    Before this there was selected area with error was this one: {30,10,15} but after I went to Tools->Options->International and I unchecked "Use sistem separators"

    Now the selected area with an error is the one in the picture
    Attached Images Attached Images

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Drop down menu and product

    see attached - when you open on your own settings the formula should adapt to your settings.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-07-2009
    Location
    Romania
    MS-Off Ver
    Office XP
    Posts
    4

    Re: Drop down menu and product

    yes...works perfect
    I have attached a print screen so you can see how the formula looks with my settings...

    many many thanks
    Attached Images Attached Images

+ 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