YTL Excel #122: Advanced Filter to a New Sheet (Formula Criteria)

SL Date Customer Name Customer ID Amt 1 22/02/2011 bbb 222 2343 2 19/02/2011 ccc 333 1455 3 19/02/2011 ddd 444 114 4 25/02/2011 ddd 444 114 5 20/02/2011 ccc 333 354


In above table I was trying formula criteria in excel 2007. My formula criteria is =AND($B2>=$G$5,$B2

Hi,

I need help on Advanced Filter Criteria when i have recorded as a macro.

Below are the tables, If my cirteria is like below.

Customer Product AA Cookies

I will data extracted. However, if my data is as per the table below, where i need Custore as BB and Product as Milk with AA and cookies present. I will get all data related to AA and BB extracted. Please could some one help me on getting on BB even though if AA is present.


Customer Product AA Cookies BB Cookies BB Milk


Date Customer Product Total 2/2/2010 AA Cookies 2 2/2/2010 AA Cookies 2 2/2/2010 BB Cookies 3 3/2/2010 BB Cookies 3 3/2/2010 BB Milk 4 3/2/2010 CC Milk 4 4/2/2010 CC Milk 4 4/2/2010 CC Milk 2 4/2/2010 CC Brownies 2

Hello,

I have a pre set criteria range on one worksheet that looks at 5 different rows of criteria. I dont always require 5 rows of criteria to fulfill my filtering, however, how i currently have it set up means that the advanced filter still looks at those rows, sees not filters and returns all the data.

How can you write the macro to return only the rows that contain data?

Any help woud be much appreciated. I can provide a work sheet sample if it is easier.

Regards
Ed

Hello,

I need help!!
I currently have a criteria range for an advanced filter starting in column J, that is dynamic and needs to be changed for every output for different requirements.

The Advanced filter works fine when you include a piece of filter data in the first column. However when this filter is not needed and you start the filter from the second or later column, column K-O, it returns all of the data stored at the source as it is not registering there is a filter in place.

My range is currently defined as:
=OFFSET(Sheet2!$J$1,0,0,COUNTA(Sheet2!$J:$J),COUNTA(Sheet2!$1:$1))

I have been told i need to limit the CriteriaRange (the code above) to ignore column J, and start from column K but as i am very new to excel i havent got a clue how to do this!

The macro i recorded to execute the advanced filter is:
Sub AdFi()
Sheets("Sheet1").Range("FILTERRANGE").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("CRITERIARANGE"), CopyToRange:=Range("A15:H15"), Unique:= _
False
End Sub

Any help in solving this and working out how to ignore the first cell if it is blank would be greatly appreciated. It ignores blank cells if there is a filter in the first column, but for example not in column K but a filter in column L. So i am confused.

Thanks
Ed

Hello,
I have 3 sheets. One is my data sheet, one is the results sheet where I want my filtered data to display and one sheet is the one where i have 5 comboboxes that are supposed to filter the date from my date sheet. What I want to do is somehow use the comboboxes (each has one criteria) to filter the data from the data sheet and take that filtered data and display it in my results sheet.

Is that possible at all? I tried to use the advanced filter but it doesn't accept my comboboxes as criteria. Also I don't know how I can connect my combos so that one variable depends on the other.

I am fairly new to excel and VBA. So far I have mostly used recorded macros but I guess that won't do it this time.

Please help me!! Thank you soooo much!!!

I am trying to use Advanced Filter with two criteria - perhaps it just can't work the way I want?

Here is my example:
My Data:

Class Teacher Math Frank Math John Math Frank English Ted English Alice English Frank Science Mary Science Mary Science Mary

Here is my criteria:

Teacher Class Frank Math John Frank Ted

I am EXPECTING the following:
Class Teacher Math Frank Math John Math Frank

Here is what I GET:
Class Teacher Math Frank Math John Math Frank English Ted English Frank


Is there any way to do what I want?

Gene Klein

I am trying to establish criteria for an advanced filter in which all rows will be exposed if "RPL1" is found in column A, and/or D, and/Or F, and/or J.

Jenn

Hi,
I need to filter out:-
two product types NYK and ZCH from the Product field
filter out blanks from the second field
filter out records with only actuals in the third field.
Can anyone help on writing a advanced filetr for this. I wrote the below but doesnt work:-

Prod Prod TYPE
"NYK" "ZCH" Actual =J2 ""
where J is the column, I want the blanks to be filter out for all products.

Thanks,
Amit

Hi all, I have below table, i want to do use advanced filtering to do
filtering based on this criteria ColA"D" And ColA"B" And ColA"C". Is it possible that i can put this filtering criteria into one cell instead of three different cell under 3 headings called ColA? Thanks

ColA ColB ColC
--- --- ---
A B C
AA BB CC
AAA BBB CCC

I have an advanced data filter coded in VBA using the input range, a criteria range (on a separate sheet), and an extract range. One of the fields in the data range is headed "Event". This column can have one of about ten various entries. I'd like my criteria range to select all data items EXCEPT those containing one or two of the possible entries. For example, I'd like to extract all rows that have an Event OTHER THAN say, "Practice" or "Tournament". I know the criteria range can be used to select various AND / OR items but this is kind of a reverse situation.

Thanks

Kevin