Drop Down List dependant on 2 parameters

[BlueGunner]

This is my first post and wish to get some help.

I have a spreadsheet with two columns. One column is Luminaire Manufacturer and the other is Luminaire Name.

Each column has multiple.

Say for Luminaire Manufacture I have 4 different companies

The Columns would look like this this
Company - Fitting

Company 1 - Type 1
Company 2 - Type 2
Company 1 - Type 3
Company 1 - Type 1
Company 3 - Type 4
Company 2 - Type 5
Company 2 - Type 2
Company 4 - Type 6
Company 1 - Type 1

I would like a drop down list that would dependant on which Company is chosen from a separate drop down list and the option they be given are the fittings available from that company.

I would also like no repeats in the options.

I don't know where to start. This is linked up with an access spreadsheet which I am way more comfortable with.

Thanks in Advance

BG

I want to do a drop down list which

[JBeaucaire]

'DEPENDENT DROP BOXES
These are called Dependent Drop Down validation 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

'Unique Values from Repetitive data in a drop box
http://www.excelforum.com/attachment...dationlist.xls

[NBVC]

See attached.

First name the Unique company list something like "Company" without quotes.

Do this by selecting the list and enter Company in the Name Box (just to the left of the Formula Bar).

Then go to the cell you want the drop down list in, and go to Data|Validation and select List... then enter =Comany in the source field.

Then,

Assuming your database is in A1:B10 of Sheet1
Assuming your drop down list is in A1 of Sheet2


Go to C2 of Sheet 1 and enter formula:

Please Login or Register  to view this content.

copied down.

Then in Sheet2, B2 enter:

Please Login or Register  to view this content.

Then in A2 enter:

Please Login or Register  to view this content.

and copy down as far as you want to ensure all possible fittings would always be captured.

You can hide any unwanted colums so you don't see the helper column or the formula in B1.. if desired.

[BlueGunner]

Oh wow... thank you both... this has seriously been tremendous help.

[JBeaucaire]

Glad we could help.

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