Using Advanced Filter in Excel

[Lorna]

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.

 

[Ragnar1211]

Try this page: http://www.contextures.com/xladvfilter01.html and
http://www.contextures.com/xladvfilter02.html

It has simple and advanced examples of advanced filtering.

 

[Domski]

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

 

[lenze]

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

lenze

 

[Lorna]

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.

 

[lenze]

Change this line

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Private Sub Worksheet_Change(ByVal Target As Range)

There is a differnce!!

lenze

 

[Lorna]

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.

 

[Weaver]

What happens if you include

Application.EnableEvents = False

at the start of your code
and

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.

 

[Lorna]

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.

 

[Lorna]

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.