Drop down box

[Vol4Ever]

I know how to create a drop down box that populates with data from a range of cells. However, I had a spreadsheet from a friend one time that had a feature I'm trying to put in one of mine. I can't remember the command to do it. Here is a sample data set:

DATE CUSTOMER NOTES
1/1/2008 Customer 1 Visit notes
2/1/2008 Customer 2 Visit notes
3/1/2008 Customer 3 Visit notes
3/1/2008 Customer 1 Visit notes
4/1/2008 Customer 1 Visit notes

I'd like to put a drop down box at the top of the page that dynamically updates as rows are added with new customers. Whenever a certain customer is selected, only the rows for that customer appear. In other words, selecting "Customer 1" would yield:

DATE CUSTOMER NOTES
1/1/2008 Customer 1 Visit notes
3/1/2008 Customer 1 Visit notes
4/1/2008 Customer 1 Visit notes

Seems there is an internal command already inside Excel to do this. Can someone refresh my memory? Thanks!

 

[Vol4Ever]

EDIT: The filter command is getting me close to where I want to go, but it seems like it would get cumbersome to use once there was lots of customers in there. See attached file.

 

[SkipVought]

Hi,

"...but it seems like it would get cumbersome to use once there was lots of customers in there."

How would a ComboBox be less cumbersome?

How many Customers is lots?

Granted, a list of thousands can be cumbersome. In that event, you might want to divide your list, perhaps A-M and N-Z, or something similar.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and that can still be accomplished using AutoFilter, via a column with a formula like this, to return the first character...
[tt]
=left(b2,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Vol4Ever]

Probably 100 or less. What I meant by cumbersome was that the filter feature makes you clear check boxes & then scroll down & place checks next to the ones you want to see. There would be just as many in the ComboBox, but it would be a simpler selection process. One click instead of multiple.

 

[SkipVought]

"...the filter feature makes you clear check boxes & then scroll down & place checks next to the ones you want to see."

Are you referring to your posted sheet? There are absolutely NO checkboxes.

The behavior that you describe is characteristic of PivotTables, not AutoFilter, the latter of which is what your sheet demonstrates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Vol4Ever]

Are you referring to your posted sheet? There are absolutely NO checkboxes."

That's weird. There are checkboxes on mine when I click on one of the filter arrows. See attached screenshot.

 

[SkipVought]

You have Excel 2007, an important piece of information.

Actually the 2007 AutoFilter has more functionality than previous versions.

So what advantage would a ComboBox have, in your opinion, for 100 of fewer customers (which is a really small amount)?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Vol4Ever]

Check boxes just cause extra button clicks.

Click #1 - Clear "Select All" field
Click #2 - Scroll down to desired value
Click #3 - Put check in proper box
Click #4 - Click OK

With a combobox, it would just be scroll to desired value & click it. Just makes it more user friendly.

 

[SkipVought]

You'd need a Control Toolbox control.

You'd want mulit-select in the control.

I'd use a dynamic named range and assign the Range Name to the ListFill property. faq68-1331



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Vol4Ever]

Oof. What a mess. Now I can see why you were steering me clear of that option! Looks like my users will just have to get used to the checkboxes. At least I got the filter option figured out. That's farther than I was when I got here this morning.

Thanks for the tips.