Need to create a dependant drop down controlled through 2 prior drop downs

i have a small business and am trying to speed up and improve the way i contruct my quotes / Purchase orders. I attach the workbook i am working to create to ask for some pointers to help me develop the workbook but also my skills. I have three sheets setup identically headed cable, conduit and lighting. in these sheets i have a table that shows the parts and manufacturer and a list of potential suppliers that when populated will allow me to derive sensible buying decisions

I have created names for the drown downs' on the calculation sheet and while the indirect function allows me to create a dependant list for the parts associated with either cable, conduit or lighting, its when i try to filter the parts list down further by manufaturer that I have hit a wall so to speak. I did think that using a drop down list that had the following =INDIRECT(SUBSTITUTE(B22&C22," ","")) in the source box would create a dependant drop down list that referenced category either( cable. conduit, lighting) & overlayed the supplier ( generic, wylex etc) to provide a drop down list that contained only those parts that were for example conduit parts that were manufactured by telco which if only a list of five items. Well it doesn't so am looking for pointers to help me

my questions are based on the attached workbook1. do I need to have my data in a different format?
2. What function / combination should i be looking to use to solve my issue.
3. On the calculation sheet the formula's i have used in column's E and F work but as I increase the catorgories is there a better way as the formula will simply get larger and larger.

Many thanks for any help or guidance you could provide

Mark Electrical price list testing sheet.xlsx

I might have time to look at this a little more tomorrow, but in case you have not yet checked out this website, it provides great info on the subject:

http://www.contextures.com/xlDataVal02.html

Does this help you at all? It would be my guess that the problem is in the setup and in the naming of the lists.

-nise23

Originally Posted by nise23

I might have time to look at this a little more tomorrow, but in case you have not yet checked out this website, it provides great info on the subject:

http://www.contextures.com/xlDataVal02.html

Does this help you at all? It would be my guess that the problem is in the setup and in the naming of the lists.

-nise23

Hi Nise23

Thanks for the site you directed me to. I agree it does look like i need to redesign the setup of how i construct the worksheets and the naming of the lists. I will have a detailed look & post what I have found and any help /suggestions will be greatly recieved.

Many thanks
Mark