I have a table with company names on row 1 with contact names under each one.

I want to have drop down with the company names (Easy bit) but then another
drop down underneath with the contact names for that company.

I have defined a range "Company" for row A, but I'm trying to do it without
having to define ranges for each company. Is it possible ?

Thanks

Hi Gibby,

You can accomplish your goal with Excel.

Here, I think the best way to explain is to have you complete an example,
and you'll understand the method.

Open up a new Excel Workbook.

Enter in CELL B1: Dog
Enter in CELL B2: Cat
Select CELLS B1 & B2 (hilight them) - Right Click, and Select "Name
Range"--Use the name: BREEDS

Enter in CELL C1: Golden Retreiver
Enter in CELL C2: Great Dane
Enter in CELL C3: Lab
Select CELLS C1, C2, C3 (hilight them) - Right Click, and Select "Name
Range"--Use the name: DOGS

Enter in CELL D1: Siamese
Enter in CELL D2: Tabby
Enter in CELL D3: Minx
Enter in CELL D4: Leopard
Select CELLS D1, D2, D3, D4 (hilight them) - Right Click, and Select "Name
Range"--Use the name: CATS

Click on CELL A1.
Next Click on the Excel Menu- DATA - and click on the option: "Data
Validation"
Under "Settings"
On the "Allow" tab, change it to 'List', and for the source, use: =BREED

Click on CELL A2.
Next Click on the Excel Menu- DATA - and click on the option: "Data
Validation"
Under "Settings"
On the "Allow" tab, change it to 'List', and for the source, use:
=Indirect(A1)

Now, you should be able to select either "Dogs" or "Cats" from the listbox
in cell A1, and once you do, depending on your choice, you should be able to
pick from a second listbox in cell A2. Should be what you're looking for?

Should work as you've asked for it to work. You should be able to apply
this same technique to the workbook of your choice.

If you have additional questions, feel free to contact me, and I'm more than
happy to send you a workbook with an example if it helps.

I hope this was very clear for you, and I hope it helps solve your problem :)

--
Best of Luck!,

Chris Van Nuys

Become more effective and efficient: Drive your business career by
mastering Excel!


"gibbylinks" wrote:

I have a table with company names on row 1 with contact names under each one.

I want to have drop down with the company names (Easy bit) but then another
drop down underneath with the contact names for that company.

I have defined a range "Company" for row A, but I'm trying to do it without
having to define ranges for each company. Is it possible ?

Thanks

Hi Chris,

I was trying to avoid using ranges for the companies. And i have these on a
separate sheet called contacts. Can't get indirect to work



"Chris Van Nuys" wrote:

Hi Gibby,

You can accomplish your goal with Excel.

Here, I think the best way to explain is to have you complete an example,
and you'll understand the method.

Open up a new Excel Workbook.

Enter in CELL B1: Dog
Enter in CELL B2: Cat
Select CELLS B1 & B2 (hilight them) - Right Click, and Select "Name
Range"--Use the name: BREEDS

Enter in CELL C1: Golden Retreiver
Enter in CELL C2: Great Dane
Enter in CELL C3: Lab
Select CELLS C1, C2, C3 (hilight them) - Right Click, and Select "Name
Range"--Use the name: DOGS

Enter in CELL D1: Siamese
Enter in CELL D2: Tabby
Enter in CELL D3: Minx
Enter in CELL D4: Leopard
Select CELLS D1, D2, D3, D4 (hilight them) - Right Click, and Select "Name
Range"--Use the name: CATS

Click on CELL A1.
Next Click on the Excel Menu- DATA - and click on the option: "Data
Validation"
Under "Settings"
On the "Allow" tab, change it to 'List', and for the source, use: =BREED

Click on CELL A2.
Next Click on the Excel Menu- DATA - and click on the option: "Data
Validation"
Under "Settings"
On the "Allow" tab, change it to 'List', and for the source, use:
=Indirect(A1)

Now, you should be able to select either "Dogs" or "Cats" from the listbox
in cell A1, and once you do, depending on your choice, you should be able to
pick from a second listbox in cell A2. Should be what you're looking for?

Should work as you've asked for it to work. You should be able to apply
this same technique to the workbook of your choice.

If you have additional questions, feel free to contact me, and I'm more than
happy to send you a workbook with an example if it helps.

I hope this was very clear for you, and I hope it helps solve your problem :)

--
Best of Luck!,

Chris Van Nuys

Become more effective and efficient: Drive your business career by
mastering Excel!


"gibbylinks" wrote:

I have a table with company names on row 1 with contact names under each one.

I want to have drop down with the company names (Easy bit) but then another
drop down underneath with the contact names for that company.

I have defined a range "Company" for row A, but I'm trying to do it without
having to define ranges for each company. Is it possible ?

Thanks