Multiple Dependant Drop Down Lists

Hello All,

I am working on a spreadsheet for my Building company. I'm building a tool to help me price for Fascia, Soffits and Cladding.

I'm using Macbook Pro Microsoft Office for Mac 2011

Please see attached file

The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.

The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.

Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"

So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C

The Value column would then show a value based on the options selected.

I'd really appreciate any help with this matter.

Kind Rega

please have a look the the following page as an example , i think its the is same
http://www.contextures.com/xlDataVal02.html

Can anyone please spare some time to help with this???

Thanks
Stewart

Thanks Piet Bom. You seem to have understood perfectly what my intentions are, however I cannot seem to work out how you have done it. Could you tell me what you have done so that i can copy it along to the rest of the lists.

Thanks again

Regards
Stewart

I sorted the categories in order so that they were easier to deal with and I discovered that the number of categories is 31 and there are only 30 categories with products. I added a dummy listing for the missing category which is highlighted in the listing.

All illegal characters for the naming of cell ranges were eliminated so the category listing looks different. Spaces were replaced with _ and / with\ and there were others also.

Hope this helps

Thanks for your reply, both methods seem to work. Only thing I have to do now is work out how to stop the cost column from showing #REF! and add a total at the bottom that adds up the cost column.

Thanks for everyones help with this.

Regards
Stewart

A total has been added and in this file, there shouldn't be errors generated. The missing description for the extra category has to be addressed or you will have continuing problems.

The error that you refer to occurs when a value is missing. In this workbook this can easily happen because of the massive category list along with the associated descriptions and prices.

I downloaded the other workbook offered. It had a macro in it but I don't enable macros so I couldn't trouble-shoot why there are REF errors.

Piet,

That looks great, Really like what you've done. Wish I had the knowledge to do that myself. Thanks so much for your help with this.

If i add a product or category to the lists sheet will the calculator sheet automatically update to include the new additions, if not, how(if possible) do I do this. Now that I have this sheet working I might try and add suppliers into it and build it to be an ultimate pricing tool.

Would you be prepared to help with that, now that i've seen what a wiz you are lol

Regards
Stewart

Anyone available to lend a hand???

Regards
Stewart

Anyone available to lend a hand???

Regards
Stewart

If you click in the FSG Materials Calculator, and on the Insert tab, click on Table, that will change the materials calculator into a Table that when you get to row 31 and enter the quantity, if you hit the tab key twice a new row will be added automatically and the calculation cells at the bottom will be moved down and include the new row(s).

The only thing that you seem to be missing is the Total at the bottom which is =SUM(E35:E37)

I don't know where your labour costs are coming from but there is an opportunity to have that be calculated from however you calculate the labour. The discount could be automated based on the total in E34 instead of choosing from the Data Validation list. To do this, the list on the Discount worksheet would have to be changed so that there would be amounts with an associated discount.

Regarding the other things that you want, if you inserted the columns where you want and supplied the list of Supplies, and an explanation of Priority. The suppliers would require a list of suppliers in order to generate a drop down listing for them and the same thing applies to the Priorities.

Piet,

All i can do is thank you so far for the excellent work you have done. Thank you for the time and effort you have given my project.

Regards
Stewart

Newdoverman,

Thanks for your message, Unfortunately I don't quite understand it. Could you elaborate at all. Have you seen Piet Bom's revised attachment. It's brilliant. I just wish I had the expertise to continue it.

Regards
Stewart

Yes, I took a look at the workbook by PietBom and it is getting very complicated very quickly. This is especially true if you have more than one supplier for many of the products that you carry. A relational database program is probably more suited to this problem.