Excel 2010 Advanced Function Filter problems - 2007 issues

[adamwestrop]

Hi,

Am having problems with advanced function based filters in Excel 2010.

I swear Excel 2007 brought back results.

Please see the attached spreadsheet.

You can see my advanced criteria range above the range, however when I try to perform the advanced filter, the range filters everything out, leaving all rows blank.

Is this a known Excel 2010 issue or do I need to do something different in Excel 2010?

Thanks

[Ron Coderre]

I can't think of a situation where Excel 2007 would have found matching items using the criteria you posted. To use a calculated criteria, you cannot use a corresponding data column heading in the criteria range. Also, the calculated criteria must evaluate to TRUE or FALSE.

Something like this would work, using your posted workbook:
E1: TestColumn....(it cannot be "Category" or any other column heading)
E2: =E6="Exact"...(Which will resolve to TRUE or FALSE)

Does that help?

[adamwestrop]

Wow, that helps a lot.

I done what you suggested, and your explanation is in plain English which is great, as people who have advanced Excel skills tend to things that go over my head!

Can't use a corresponding data column, great explanation.

Calculated Criteria must evaluate to True or False, this works when I use the IF command with the values True or False, so are you saying that with advanced filters, when using the IF command the results always need to be True or False with an advanced filter? Can you put some context around it please?

Also on wider subject, Im learning Excel, and would like to be able to be technically competent at advanced formulas etc, and Marco's and VBA, besides experience, are there any books you can point me to please?

THANKS A LOT OF YOUR HELP, IT IS MUCH APPRECIATED

[martindwilson]

have you read here?
http://www.contextures.com/xladvfilter02.html
if is redundant if testing for true/false
=a1="apples" is the same as if(a1="apples",true,false)