AutoFilter
Posted by ESPEN on January 12, 2001 12:54 AM
I would like AutoFilter to find a persons name, and all the rows that he is listed on. BUT I also want to, in the same search, see the row below his name, because the information on him is in both rows? Is that possible?
espen
Posted by Dave Hawley on January 12, 2001 1:11 AM
Hi espen
Sorry to say, but the short answer is no!
But let's say your names are in column A and on the row below in Columns A:F is you information pertaining to each person. In cell G2 type =A2 and in cell G3 also type =A2, now highlight G2:G3 and using the Fill handle drag down to the last row in your table. This will give you each name on 2 rows instead of one. Now you can filter by Column G.
Hope this helps
DaveOzGrid Business Applications
Posted by espen on January 12, 2001 2:22 AM
Ok, then I have to write the name in both row A and B, and AutoFilter will show them both.
But, let's say the Name is in column 2, and the week is in column 1. Can I ask to see only "Bill" and 4th-12th week? Something like (if(column2=Bill,(if(column1>=12,(if(column1<=4,true;false))))))???
Posted by Robert on January 12, 2001 4:07 AM
To do that use the custom selection of the autofilter and use greater than AND less than.
OK?
R.
Posted by Mark w. on January 12, 2001 7:25 AM
Using Advanced AutoFilters, this is easier than
you might think!
Let's say that cells A1:B7 contain
{"Field1","Field2";"Mary",1;"",2;"Tom",3;"",4;"Jack",5;"",6}
You can display the rows associated with Tom by
creating a criterion as follows:
1. Enter the values {"Field1";"Tom"} into cells E1:E2.
2. Enter the formula =$E$2=OFFSET(A2,-1,) into cell F3.
Next, choose the Data Filter Advanced Filter...
menu command and enter $A$1:$B$7 into the "List
range" field and $E$1:$F$3 into the "Criteria
range" field. Click OK and voila!
