Advanced Filter Criteria

[abrownbear]

I cannot make this darn thing work! Here's the deal. I have a large spreadsheet we would like to use in the interim during deployment of Microsoft Project. I have project names listed down the rows, and tasks names across the columns. Each cell contains a formula that calculates due dates for each task. This spreadsheet has about 40 columns (tasks) and 80 rows (projects). I have it conditionally formatted so that things past due turn red, things due in the next week turn green, all else remains black. I am trying to create an advanced filter so that I can view only projects with tasks due in the next week.

First problem is I'm not sure of the best type of formula to use for the criteria to say select dates after today, and before today + 10 days. I've tried a straight formula and an AND formula. The AND formula worked (returned true) when I applied it to a single cell, but when I attempted to apply it to the entire sheet nothing filtered out, sheet remained the same. Is there a function that will return true if a value falls between two other values?

Then, I'm not certain I'm laying out the criteria range properly...I want to see any record that contains any task that is due within the next 10 days--So I placed my criteria each in a different column and row of the range. That is, criteria for column 1(task 1)in A1; criteria for column 2 in B2; criteria for column 3 in C3 and so on, so they appear diagonally, all the way to AI34. According to the help file, this creates an OR function for each column. Does this seem right? Sometimes this filters out all the records, though they've turned green so I know there are some that fit the criteria. What am I doing wrong here? I should also mention that about every 3rd or 4th time I apply the filter, Excel experiences some kind of error and shuts down. I'm suspicious that the Advanced Filter just does not work properly. Also, I've tried using natural language references and direct cell/range references, same result. The directions say to use the corresponding cell in the first record for the criteria formula. Also, same result. Anybody got ANY ideas!

 

[Aladin Akyurek]

Lets say that A1:B7 houses the following:

{"newfield",0;
FALSE,0;
0,0;
"task","date";
"t1",37289;
"t2",37319;
"t3",37324}

where zeroes stand for empty cells (Note: strange looking numbers are dates.).

A4:B7 houses the actual sample data.

In A1 enter: newfield [ that is, a new name/label, different from the ones we have in A4:B4 ]

In A2 enter: =B5>TODAY() [ we're after the recs whose dates > today ]

Activate A5;
Activate Data|Filter|Advanced Filter;
Check 'Copy to another location';
Check that the 'List range' is $A$4:$B$7, if not, modify;
Enter $A$1:$A$2 as 'Criteria range';
Enter e.g., $E$4 as value of 'Copy to';
Click OK.

I get as result:

{"task","date";
"t2",37319;
"t3",37324}

in E4:F6.

Hope this helps.

 

[Aladin Akyurek]

On 2002-02-28 08:35, abrownbear wrote:
I get this and it works, thank you. Now how do I write the formula that will return dates >today (after today) AND <today+10 (before 10 days from now)?

=AND(B5>TODAY(),B5<TODAY()+10)

Note. The criteria must always return a logical value; put otherwise, they must be Boolean/logical expressions.

Aladin

 

[abrownbear]

Thank you, thank you, thank you. One more thing. The example filters a single column of dates. I need to filter several columns of dates independently, that is, I will see the row/record/project if it contains any column/field/task that occurs in the next 10 days. Where in the criteria range do I place the criteria for the other columns, or can I include all the columns into one formula?

 

[Mark W.]

On 2002-02-28 08:51, abrownbear wrote:
Thank you, thank you, thank you. One more thing. The example filters a single column of dates. I need to filter several columns of dates independently, that is, I will see the row/record/project if it contains any column/field/task that occurs in the next 10 days. Where in the criteria range do I place the criteria for the other columns, or can I include all the columns into one formula?

See the Help topic for "Examples of advanced filter criteria".

 

[abrownbear]

It does not work. I placed the above formula in A1, then B2, then C3 and so on, creating a criteria range 31 rows by 31 colums. This is the setup suggested in help to find data that meets either a condition in one column or a condition in another column. When I apply this criteria to filter in-place, it shows no records. When I apply the formula to a single cell that I know fits the criteria, it returns true, so the formula definitely works. Help only gives an example of conditions created as the result of a formula for filtering one column.
Another question: Could it be filtering out all the records because the cells contain formulas?
This message was edited by abrownbear on 2002-02-28 10:53

 

[Aladin Akyurek]

On 2002-02-28 10:17, abrownbear wrote:
It does not work

Andrew,

I thought I lost you. Mark digged up the thread back to the front page

It would help him and me if you would post a small sample of your data. If inclined to do so, select an empty cell, type =, select 10 rows worth of data, hit F9, and copy what you see and post it along with the range it occupies and expected results.

Aladin

 

[abrownbear]

Okay. I just typed it in to make it as simple as possible. This type info occupies A6:D10 -

{"Project" , "Do This" , "Do That", "Do More"
Jump, 1-Jan-02, 6-Jan-02, 2-Feb-02
Stand, 15-Jan 02, 1-Feb-02, 1-Mar-02
Run, 15-Feb-02, 27-Feb-02, 8-Mar-02
Walk, 18-Mar-02, 22-Mar-02, 1-Apr-02}

The dates are generated by formulas. I want to see only projects (Jump, Stand...) that include any tasks (Do This, Do That...) due between today (28-Feb-02) and 10 days from now (9-Mar-02). So, in this example, I would only see the tasks for project "RUN".

Note, I don't need the tasks filtered out, only the projects without due dates in the next 10 days. The criteria range (the way I've been constructing it) would occupy A1:D4, with the corresponding formulas in A1, B2, C3, and D4.

 

[Aladin Akyurek]

On 2002-02-28 11:15, abrownbear wrote:
Okay. I just typed it in to make it as simple as possible. This type info occupies A6:D10 -

{"Project" , "Do This" , "Do That", "Do More"
Jump, 1-Jan-02, 6-Jan-02, 2-Feb-02
Stand, 15-Jan 02, 1-Feb-02, 1-Mar-02
Run, 15-Feb-02, 27-Feb-02, 8-Mar-02
Walk, 18-Mar-02, 22-Mar-02, 1-Apr-02}

The dates are generated by formulas. I want to see only projects (Jump, Stand...) that include any tasks (Do This, Do That...) due between today (28-Feb-02) and 10 days from now (9-Mar-02). So, in this example, I would only see the tasks for project "RUN".

Note, I don't need the tasks filtered out, only the projects without due dates in the next 10 days. The criteria range (the way I've been constructing it) would occupy A1:D4, with the corresponding formulas in A1, B2, C3, and D4.

OK. This is your sample data along with the area that holds the criteria:

{"newfield",0,0,0;FALSE,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;"Project","Do This","Do That","Do More";"Jump",37257,37262,37289;"Stand",37271,37288,37316;"Run",37302,37314,37323;"Walk",37333,37337,37347}

Zeroes stand for empty cells & funny looking numbers for dates (that is what I wanted you to do: when I get a sample this way, I don't have to enter the data manually, so you'll get a bill for forcing me to type over the sample you provided :wink: )

Enter as criteria:

=OR(AND(B7>TODAY(),B7<=TODAY()+10),AND(C7>TODAY(),C7<=TODAY()+10),AND(D7>TODAY(),D7<=TODAY()+10))

and apply advanced filter.

It gives me:

{"Project";
"Stand";
"Run"}

as result.

Aladin

 

[abrownbear]

YOU are a complete genius. This works! Only problem: I have 31 columns, and can only put 30 arguments into a formula.

After all this, I'm just going to erase a task because you are genius and I'm using this!

Thank you for working on this and next time I will follow your instructions to avoid all that nasty typing.

Problem solved, thank you so much!