Trying to create a simple quoting database

Im trying to create a quoting system. im trying to get a drop down list of all boat builders and from the builder you select,id like all of the models in a second drop down down list. and when i've got that id like all of the rig details to pop up so i can work formulas off of the measurments to quote for sails. im a beginer and can just about make a drop down list. also is there a way to take a table of a website and put it into excel?

Thanks!!

Originally Posted by sprattuk

I'm trying to get a drop down list of all boat builders and from the builder you select, I'd like all of the models in a second drop down down list.

These are called Dependent Drop Down lists and they use named ranges and INDIRECT() references for Data Validation lists in the proceeding drop boxes.

Here's a sample sheet I've posted showing how it's done. The sheet "CHOICES" has the dependent drop lists (and shows one of the dangers of using them) and the sheet "LISTS" shows the lists and named ranges in use.

Have a look.

Sample Files

Jerry Beaucaire's Excel Tools

The files you want to examine are:
--DependentLists3.xls - 3 levels
--DependentLists2.xls - 2 levels
--DiminishingDropDownList-NonArray.xls
--DependentLists-SelfSorting.xls - Self-Sorting lists of fruits by color choices

More reading:
http://www.contextures.com/xlDataval02.html
http://www.contextures.com/xlDataVal13.html

When I've got that I'd like all of the rig details to pop up so i can work formulas off of the measurements to quote for sails.

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

INDEX/MATCH explanation
http://www.excelforum.com/excel-gene...ame-row.html#3

Is there a way to take a table of a website and put it into excel?

Look at the Import Data wizard found under Data > Import External Data > New Web Query.

Thank You!!!

I'v got the data now. looking at how complicated it is im just going to have one list with maker and model. but could you please explain how i could link that to make the rig details come up next to it? sorry im a real novice. This is where im at so far.... i'v selectede the boat and manually put in its measurements from which the sail areas have been calculated with formulas. id like them to appear there in the red section automatically when the boat is selected?

I have a dynamic double-data-validation-in-one-cell technique published here:

Sub-Data-Validation Lists from a Master DV List

I've adapted it into your sheet for you.

On your Rig Data sheet you need to keep a "short list" of the "single item" brands and the main entry for the multi-item brands. That column is used for a named range called YachtsShort and is used on your Quote Sheet in the selection cell with the selection cell is currently empty.

Once a selection is made in that cell, use the same cell again to make a second choice from the sub-choices, if any exist.

The rest of the cells in red will fill themselves out based on a simple INDEX/MATCH formula.


NOTE: Typos and abbreviations will break this method, so be sure to clean those up. For instance ANGELMAN and ANGLEMAN, and the abbreviated BUCNR had to be removed/corrected.

I don't know how long it will take, but it is a one-time process, so that's a good thing at least.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.