Defining Names with Combo Box Form in Excel

[westma]

My objective is to have two combo boxes, with the second box populating depending on what is selected in the first box.

Currently this is performed by the following:

1. The first box populates from a list (A2:A17)and outputs the number of the selection to cell A20.

2. Cell B20 uses VLOOKUP to match the number in A20 with another range of cells that corresponds to the first combo box selection (say B2:S2 match the number 1, B17:A17 match the number 16). The output of this cell is then 'Sheet'!B2:S2.

3. A name is defined, called X1Y1, and refers to =INDIRECT(Sheet!B20).

4. The second combo box's input range is then the name X1Y1, so that it populates with whatever is contained in B2:S2.

This works well, except that a name must be defined for every second combo box. I am looking at having about 120 pairs of combo boxes, which means 120 names to define. This is very tedious, and will not work well if someone else has to maintain the spreadsheet later. Is there a way to bypass this loop from one combo box to the other, (without using macros preferably)? If we could enter a function into the combo box input range, like =Indirect(), this would solve our problem, but I can't seem to make this work. Any suggestions? Thank you!

 

[SkipVought]

Hi,

This question has been addressed many times.

Check

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

as an excellent reference for dependent drop downs.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[westma]

Thanks Skip, I found what I needed. Thanks for pointing me in the right direction.