more than 100 items on drop down list

[Dan15]

Here is the situation- I have two drop down boxes, the first box triggers the second. The first drop down contains a list of Geneneral Contractors, the second drop down contains the contacts at each general contractor. There are maybe 15 General Contractors, up to 20 contacts for each. How do I set up the second drop down so I can still see the contacts when it goes over 100?

 

[SkipVought]

Hi,

What so magic about 100?

Use the result of your first dropdown to select the GC in a pivot table that will give you the Contacts for your second. In fact, you could do the whole thing with a Pivot Table with GC in the Page field. You select the GC and directly see the Contacts.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[Dan15]

It may just be my computer. I tried it on another machine and it worked fine. For some reason, on my machine the bottom portion of the drop down is not visible and the scroll will not work. So the first half of the contacts show up, but the second half are unaccessable. I guess this is just an error in the excel on my machine.

 

[SkipVought]

Are there that many contacts for a GC? Skip,
Skip@theofficeexperts.com

 

[xlbo]

Bet you've got some frozen panes
check your Window menu (in excel)
If it has an option to Unfreeze Panes, do so and you'll probably get your list back Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Dan15]

I tried the frozen panes, and it appears everything is unfrozen already.

 

[SkipVought]

Dan,

Back to my former suggestion -- use a Pivot Table with GC in the Page field and Contacts in Row field.

You select a GC and immediately see the Contacts for that GC -- NO additional programming! Skip,
Skip@theofficeexperts.com

 

[Dan15]

You may be right Skip, I have to figure out Pivot Tables first so I'll have to get back to you-

Thanks-

-Dan

 

[xlbo]

D'oh - oh well then
I'd definitely go with Skip's idea tho
Pivot tables are dead easy - as long as the data is in this format:
General Contractor Contact
GC1 C1
GC1 C2
GC1 C3
GC2 C1
etc etc
Just select your list of Contractors and contacts (including headers) and follow:
Data>Pivot Table Report
Go thru the wizard (your range will already be selected for you ) and when you get to the Pivot Table "Positioning", drag and drop "General Contractor" into the PAGE position and then drag and drop "Contact" into the ROW position. Finally, just drag "Contact" into the DATA position and change to COUNT of CONTACT - you only have to do this as a Pivot Table MUST have a data field
et voila

If there's any stars to give for this, give it to Skip for the idea Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde