Oh, but you can use formulas which return TRUE or FALSE as criteria too. For calculated criteria like this, you must leave the column header blank. For example, in the helpfile:
Quote:
Criteria created as the result of a formula
You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:- The formula must evaluate to TRUE or FALSE.
- Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way: =''=entry''
- Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match). If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.
- The formula that you use for criteria must use a relative reference to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
- All other references in the formula must be absolute references of criteria created as the result of a formula.
|
So with your formula:
=AND(E4>F9,D7<A5,M3>G1,B1=“FY 2010”)
It should return TRUE if all 3 criteria are met or FALSE if they aren't. When you use it in your criteria range, provided you have followed the rules in the helpfile, it should act as a filter accordingly.