Using Advanced Filter in Excel

Lorna

New Member
Joined
Mar 12, 2009
Messages
19
I am currently running Excel 2003 in Windows XP. This is my question. I have a database of products I'm storing on a sheet named Database, which the user never sees. On another sheet call Input, I have four drop-down boxes which allows the user to:
Step 1. Select products filtered by one of four choices. I have done this simply using a Data Validation List.
Step 2. The second drop-down contains another Data Validation List which uses a series of nested IF statements to filter the choices available in the list based on what was chosen in Step One.

Now is when it gets complicated

Step 3. The third drop down should only display those products which are valid choices based on steps 1 and 2. There are too many choices to use nested if statements. I can successfully filter the information I need using an Advanced Filter, but I don't know how activate it. I want it to filter the list of choices in the third drop-down as soon as a choice has been selected from the second drop-down.

Step 4. Needs to do the same thing as Step three, but now filtered based on all three previous choices.

Any help would be greatly appreciated.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Maybe do a search of the board for "Cascading Dara Validation". There should be a few examples that do what you want i think.

Dom
 
Upvote 0
Use a WorkSheet_Change Event on the 3rd dropdown to trigger your Filtering code(s).

lenze
 
Upvote 0
Thanks so much for all of your help. This is what I have done so far, I've created the code to using an advanced filter to extract the data for the third and fourth dropdown boxes. I created a named range for each of the validation lists to use as its source that refers to the desired column from the extracted data.

I first tested the code as two separate macros to make sure it would produce the desired results, which it does. Next I copied and pasted the code from the macros to the Change event for the Worksheet as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Filter Label Size DropDown on TM Input Sheet
Application.Goto Reference:="LabelSizeData"
Range("LabelSizeData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("LabelSizeCriteria"), CopyToRange:=Range("LabelSizeExtract"), Unique _
:=True

Sheets("TM Input").Select
Range("B32:D32").Select

'Filter Other Criteria DropDown on TM Input Sheet
Application.Goto Reference:="OtherData"
Range("OtherData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("OtherCriteria"), CopyToRange:=Range("OtherExtract"), Unique:=True
Sheets("TM Input").Select
Range("B36:D36").Select
End Sub

The problem is tat sometimes it will work on the first changed value, but most of the time it just produces an error and stops the code in debug mode. What I want to have happen is that after the user has made selections from the first two dropdowns, then the code will run for the third dropdown. Then after a selection has been made from the third dropdown, then the code for the fourth dropdown will run.

I know how to do this in Access, but Excel is so much different. How do I trigger code for a particular cell?

Thanks again.
 
Upvote 0
Change this line
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
to
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
There is a differnce!!

lenze
 
Upvote 0
I made the change, as you suggested, but I stil get the same error message anytime a change is made to the worksheet. Any other ideas as to what may be the problem?

Thanks again for your time.
 
Upvote 0
What happens if you include

Code:
Application.EnableEvents = False

at the start of your code
and

Code:
Application.EnableEvents = true

at the end?

even if it doesn't help this time, I believe it's always a good idea to include this in wouksheet.change event driven routines.
 
Upvote 0
Thanks, that stopped the errors from occuring, but the code is not executing at all. I'm sure there's just some little something that I'm not seeing.
 
Upvote 0
Maybe it would help if I show you some of my data. The first sheet in the workbook is the input sheet called Food Rotation where the dropdowns are located. The second sheet is the Validation Lists sheet where the actual filtering takes place. Following is a sample list of some of the data from the Data List:

<TABLE style="WIDTH: 318pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=423 border=0 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Label Type</TD><TD class=xl23 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Label Use</TD><TD class=xl23 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>Label Size</TD><TD class=xl23 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=165>Other</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Cold Temp</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1"</TD><TD class=xl25 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 3 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Cold Temp</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 5 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Cold Temp</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 7 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Cold Temp</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English & Spanish</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1.5"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English, Spanish & French</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>2" X 3"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 3 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>2" X 3"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 5 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>2" X 3"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 7 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>DuraLabel</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Date / Time</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1.5" X 1"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English & Spanish</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>DuraLabel</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1" X 3/4"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English, Spanish & French</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>DuraLabel</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 62pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=82>Day of Week</TD><TD class=xl24 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 56pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=75>1.5" X 1"</TD><TD class=xl26 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: #ffffff; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Duo-Dots 3 Day</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>SupeRemovable</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 62pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>Prep</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>2" X 2"</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>Japanese</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>SupeRemovable</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 62pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>Product</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>2" X 4"</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English ONLY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>SupeRemovable</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 62pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>Shelf Life</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=75>2" X 4"</TD><TD class=xl27 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 124pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=165>English ONLY</TD></TR></TBODY></TABLE>


Following is the Criteria Range for the dropdown that filters for Label Size:
<TABLE style="WIDTH: 155pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=206 border=0 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Label Type</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=105>Label Use</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl25 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD></TR></TBODY></TABLE>

Following is a sample of extracted data using unique records:

<TABLE style="WIDTH: 236pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=314 border=0 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Label Type</TD><TD class=xl25 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Label Use</TD><TD class=xl25 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>Label Size</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>1"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>1" X 1.5"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>1.5"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>2" X 3"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 76pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=101 height=17>Dissolvable</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 79pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=105>Day of Week</TD><TD class=xl26 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 81pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=108>3/4"</TD></TR></TBODY></TABLE>

The cells from the extracted data containing the label sizes is a named range called FilteredLabelSizes. The third dropdown on the Food Rotation sheet refers to this range as its data source. I have used the same method to extract data for the fourth dropdown that filters for Other label options.

To test my code, I created a macro that performs an advanced filter with the data for the third dropdown. I attached this macro to a button on the Food Rotation sheet. I did the same for the fourth dropdown. The buttons work perfectly to filter the data, so I know the code works...I just don't want the user to have to clicks buttons to make it work.

So once again, this is what I have attached to the Food Rotation Worksheet SelectionChange event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Filter Label Size DropDown on Food Rotation Sheet
Application.EnableEvents = False

Application.Goto Reference:="LabelSizeData"
Range("LabelSizeData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("LabelSizeCriteria"), CopyToRange:=Range("LabelSizeExtract"), Unique _
:=True

Sheets("Food Rotation").Select
Range("D21").Select

'Filter Other Criteria DropDown on Food Rotation Sheet
Application.Goto Reference:="OtherData"
Range("OtherData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("OtherCriteria"), CopyToRange:=Range("OtherExtract"), Unique:=True
Sheets("Food Rotation").Select
Range("D24").Select

Application.EnableEvents = True
End Sub

Sorry for such a lengthy post. I'm sure there's probably a better way to do this and I'm certainly open to any suggestions. Again, thanks to all of you who have taken the time to reply.
 
Upvote 0

Forum statistics

Threads
1,222,415
Messages
6,165,894
Members
451,993
Latest member
rowebca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top