Results 1 to 4 of 4

Thread: Help with dropdown calculating get from list.

  1. #1

    Help with dropdown calculating get from list.



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

    Hi Guys,

    I am new to this forum and quite new to Excel and I would really appreciate your expert help. I have tried my best youtubeing and googlein but have come to a dead end since I dont even know what I am really looking for Tables, lookups, matches? Dunno anymore.

    Scenario: I have a big-big stash of computer-parts and other electronics that I am going to sell. I have a price tag on everything in my excel list. When a buyer contacts me I want to use a conditional dropdown list to get to the pricetag without having to scroll my fingers off.

    I have attached a best-I-can-do template in the thread. Basically I want to be able to get the price by choosing options in the dropdown menus.

    What I would like Excel (and you of course) to help me with is: If I choose i.e "Nvidia" - "GPU" - "1 year" from dropdownmenus in sheet1, excel gets info "IF brand=Nvidia AND product type=GPU AND age=1 year;; THEN get price from sheet 3 list to new free cell.

    Hope you have a slight clue about what I mean.

    Thank you everybody!
    Attached Files Attached Files

  2. #2
    Acolyte Azumi's Avatar
    Join Date
    Jan 2014
    Location
    Indonesia
    Posts
    30
    Articles
    0
    Excel Version
    2010
    Array entered , put this on sheet1

    =INDEX('Table or list'!D2:D5,MATCH(1,('Table or list'!A2:A5=C6)*('Table or list'!C2:C5=E6),0))
    Last edited by p45cal; 2017-01-19 at 04:40 PM. Reason: disabled the smilies

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,569
    Articles
    0
    Excel Version
    365
    You can save yourself a lot of bother updating the dependent data validation dropdowns by using a pivot table with slicers as in the attached on Sheet2 (and you won't need a sheet List).[There is nothing special or difficult about the pivot table, it takes less that 30 seconds to add - including the slicers.]
    The slicers act like your dependent dropdowns, highlighting only available options and putting them at the top as you narrow down your search. What's more,

    • if you don't select an age, you can see the prices of all the ages for a given product and/or brand.
    • you don't have to select things in a particular order; your customer might be looking for a 1 year PC, select only those criteria from the right two slicers and you can give your customer a range of brands to choose from
    • you can select multiple items within each slicer with the usual combinations of mouse-click and Shift or Ctrl on the keyboard

    Not required, but I've made the Table or list data into a proper Excel Table so that you can add to it and the pivot table data source won't need to be updated manually - but the pivot would need to be refreshed (right-click on the pivot or slicer and choose Refresh).

    Separately, and independently, in sheet1 I've implemented a version of Azumi's solution.
    Attached Files Attached Files
    Last edited by p45cal; 2017-01-19 at 06:42 PM.

  4. #4
    I finally got the match+index to work and now I can create it all without copying formulas, good experience!

    Next project is using the pivot tables, seems much easier and the updating and adding possibilities are on a whole new level; no need to remake everything after adding a new product.

    I am very grateful for your help Azumi and p45cal!

Posting Permissions

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