+ Reply to Thread
Results 1 to 7 of 7

Drop down list and working out a sum based on what has been chosen.

  1. 08-20-2019, 10:22 AM #1
    selondonbloke
    selondonbloke is offline
    Registered User
    Join Date
    08-20-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Drop down list and working out a sum based on what has been chosen.

    Hi Guys,

    Need your help as this has been driving me mad for a couple of days now.

    All i'm looking for my Excel sheet to do is if i pick an item from the drop down list, lets say "apples", it will look up my mark up of "2%" and then will give me the percentage mark up depending on what fruit item i have chosen and the amount.

    I hope i've explained the above enough for you guys to get what i'm talking about.

    Many thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-20-2019, 10:40 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Drop down list and working out a sum based on what has been chosen.

    in C2
    =B2*(1+VLOOKUP(A2,L$2:M$6,2,0))
    and copy down the column for as many rows

    Just to explain, we are looking at the Vertical table you have defined in L2:M6, finding A2 in the first column (always the first column and selecting the value in the M column for that row, column 2 in the table).

    Adding that percentage to 1 and multiplying by B2 provides B2*1.02 giving 204
    Last edited by Special-K; 08-20-2019 at 10:42 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 08-20-2019, 10:41 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Drop down list and working out a sum based on what has been chosen.

    Welcome to the forum.

    Try this:

    =B2+B2*VLOOKUP(A2,$L$2:$M$6,2,0)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 08-20-2019, 10:51 AM #4
    selondonbloke
    selondonbloke is offline
    Registered User
    Join Date
    08-20-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Drop down list and working out a sum based on what has been chosen.

    Thanks so much guys and i'm nearly there. I'm looking for the total column (C) to just give me the percentage based on the amount (B) multiplied by the mark up (M).

    Thanks again
    Reply With QuoteRegister To Reply
  5. 08-20-2019, 10:54 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Drop down list and working out a sum based on what has been chosen.

    Just this, then:

    =B2*VLOOKUP(A2,$L$2:$M$6,2,0)

    Or maybe this:

    =VLOOKUP(A2,$L$2:$M$6,2,0)
    Reply With QuoteRegister To Reply
  6. 08-20-2019, 11:25 AM #6
    selondonbloke
    selondonbloke is offline
    Registered User
    Join Date
    08-20-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Drop down list and working out a sum based on what has been chosen.

    Thanks AliGW that works perfectly.
    Reply With QuoteRegister To Reply
  7. 08-20-2019, 04:50 PM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Drop down list and working out a sum based on what has been chosen.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return value based on selection chosen from drop-down list in cell next to it
    By b624333 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-03-2014, 07:45 PM
  2. Drop Down List that an Item can only be chosen once.
    By Lewshu in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-09-2014, 02:21 PM
  3. Drop Down List that an Item can only be chosen once.
    By Lewshu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2014, 12:33 PM
  4. [SOLVED] Hide/Unhide columns in multiple worksheets based on item chosen in a drop down list.
    By Gattaca2014 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-27-2014, 01:31 PM
  5. Display different list in a list box when specific drop down chosen
    By lwwc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2013, 05:27 AM
  6. [SOLVED] Suppress drop-down list dependent on value chosen in another drop-down list
    By ptmald in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2013, 07:17 PM
  7. [SOLVED] My Excel drop-down list eliminates from list options chosen. Help
    By Sybil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 05:25 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1