• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I create a drop down menu that will select the correct value from 3 lists

gundawg

New Member
I have 3 different lists of pricing (MSRP, Contractor and Registered) that have different pricing for each of our part numbers. So the columns would be -- Part #, MSRP, Contractor, Registered.


What I am trying to do is to (on a second sheet in same book) is have someone select the pricing they need from a drop down menu (MSRP, Contractor and Registered). Then they will choose a part number from a drop down menu in each cell and the appropriate pricing will be shown dependent on the pricing the select.


I can get it to work with one list using data validation and vlookup but I cannot seem to figure out how to get have the pricing change between MSRP, Contractor and Registered.


Can someone help me?
 
Hi ,


I'll tell you what I did , and you can see if it helps :


1. Set up a price table , where the row headers are the part numbers , and the three column headers are MSRP , Contractor and Registered. In each of the cells of this matrix , put in the respective prices.


I named the following three ranges :


Price_list , referring to the entire table : =Sheet2!$E$5:$H$10 ; change this to reflect your data range. if you want , you can make this dynamic.


Part_Numbers , referring to the row headers : =INDEX(Price_List,0,1)


Categories
, referring to the column headers : =INDEX(Price_List,1,0)


2. On a separate sheet , I put in two data validation drop-downs , say in the cells E4 , and F4 ; the drop-down in E4 , was a list , with the formula : =Part_Numbers ; the drop-down in F4 was another list , with the formula : =Categories.


3. In G4 , I put in the following formula :


=IF(OR(E4="",F4=""),0,SUMPRODUCT((Category=F4)*(Part_Numbers=E4),Price_List))


Now , whenever I change the part number or the category , the price is correctly retrieved from the price list.


Is this what you wanted ?


Narayan
 
Almost! What you did did not seem to work for me. I understand data validation to make the drop down lists and how to name the data ranges. What I cannot figure out is how to match the correct pricing to the correct part number.


My thoughts:

- I don't know if using IF will let me pick the correct pricing

- I don't know if there is a way to us VLOOKUP to do this

- There must be a way to do this


I have attached a sample file at https://www.dropbox.com/s/41y1cs6536qqbxw/quote%20example.xlsx


Thanks to anyone that can help here!!!
 
HOLY COW, Narayan! You are brilliant!!! Another question...


So I want the drop down boxes to be pulled down AND the formula box that you created. When I do this, I get a value of 0 in both the formula column and the Total column. This there a way to not have the formula box and total column not have a value in them until the part number is selected?
 
Hi Gunter ,


You can use an IF statement , as shown in this :


https://www.dropbox.com/s/6xnq9b6yyl2hwcs/quote%20example.xlsx


Narayan
 
Good day gundawg


Perhaps some of the answers in this link will help


http://chandoo.org/forums/topic/changing-drop-down-menus
 
Back
Top