Multiple Drop Down Menus using numerous price lists

[Adam Wisker]

I've been spending far too long trying to work this out, so I'm hoping the experts on here may be able to help.

We have a number of different price lists from wholesalers who sell different products at different prices.

On our quote sheet front page I want to be able to have a few drop down menus, which eventually lead to a specific product, product code and price.

I have managed to Firstly have a Drop down menu which lets you select a few different wholesalers, and once one is selected, the description section indirectly chooses the item lists for that specific wholesaler.

What I'm struggling to have now is the specific product code and price to show when a specific item, for a specific wholesaler is selected. Can anyone help me here?

I've attached the workbook I'm using.

 

[Hui]

Adam

Firstly, Welcome to the Chandoo.org Forums

Recently Jeffrey Weir has written a post on this, check it out here:

http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

 

[Adam Wisker]

Hi Hui,

Firstly thank you for your reply, and thanks for the welcome to Chandoo.org Forums.

I have actually seen both of these articles before, but as I am quite a novice at Excel, it is quite hard for me to get my head around.

I was kind of hoping that someone would be able to help make the changes required, and then explain where I was going wrong so then I can build on it further. Is this something you might possibly be able to help with?

Kind Regards,
Adam

 

[jeffreyweir]

Hi Adam. Getting the specific product code and price to show when a specific item for a specific wholesaler is selected is trivial. So yes, we can help you with that.

But you have a problem in that your Descriptions list contains duplicate items. For instance, for the MEM sheet, Cat Numbers 1050200 through 1050207 all have the exact same description "Operating Shaft"

So if the user was to select 'Operating Shaft', which price should apply? I think you'll have to add another layer of dropdown where the user selects the CAT number. But how will the user know which one to select?

 

[Adam Wisker]

Hi Jeffery,

Yes I can see the problem. More work needs to be done in each of the sheets to clarify each item. If duplicates are removed/renamed, would this overcome the problem?

If yes, I will upload my document again. Would you then be able to help with the main problem of getting specific product codes and prices to show when a specific item for a specific wholesaler is selected?

Kind Regards,

Adam

 

[jeffreyweir]

Yes, and yes. What you need is probably not to remove duplicate items (because I'm sure there's a good reason why there is a range of different operating shafts with a range of different prices) but rather to make sure that there is some way to distinguish these, so the user knows what the difference between one that costs 4.58 and one that costs 10.51.

You're options are:

• CHange the description so more info is available and so that descriptions are unique.
• Don't change the description, but have an additional dropdown where the user has to choose what CAT number they want. (Or alternately, they could choose from a list of applicable prices. But I can't see how they will know how the specs differ between one that costs 4.58 and one that costs 10.51.)
• Remove those duplicates, and only have one price applicable to 'Operating Shafts' and any other such items
  
• Something else I have overlooked.

 

[Adam Wisker]

Hi Jeffrey,

Thank you, I have amended the descriptions by using the CONCATENATE between the CAT Number and Description and have amended the tables to suit, so this overcomes the duplication of any product. This has also meant I have done away with the CAT number section all together.

Now this only leaves one problem which I cannot solve, which is having the price show for the specific product. I've attached the updated workbook.

Kind Regards,

Adam