Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trying to use Advanced Filter
and failing miserably :(
I've got two sheets in my spreadsheet. Sheet1 has a subset of location numbers in ColA Sheet2 has a lot of columns, one of which is the location number. Name Hours Location Fred 8 2 Joe 10 3 Jim 6 2 I'm trying to use the advanced filter to get Sheet2 to display only those entries which have their location specified in Sheet1. I've taken the List range as being $B$1:$B$1000 (as in example above) and the criteria range as being 'Locations'!$A$1:$A$7 and, as far as I can tell, absolutely nothing happens. Any suggestions as to how I can go about this? Cheers J |
#2
|
|||
|
|||
Hello there,
If you have all the Location numbers in sheet 1 no filtering will occur in your data list. You need to replace the location numbers you do not want to see with a text string, eg. xxx. This will block the criteria line and just give you the records that have the remaining location in them. Hope this helps Judith -----Original Message----- and failing miserably :( I've got two sheets in my spreadsheet. Sheet1 has a subset of location numbers in ColA Sheet2 has a lot of columns, one of which is the location number. Name Hours Location Fred 8 2 Joe 10 3 Jim 6 2 I'm trying to use the advanced filter to get Sheet2 to display only those entries which have their location specified in Sheet1. I've taken the List range as being $B$1:$B$1000 (as in example above) and the criteria range as being 'Locations'!$A$1:$A$7 and, as far as I can tell, absolutely nothing happens. Any suggestions as to how I can go about this? Cheers J . |
#3
|
|||
|
|||
On Sheet 1, the column should have the heading 'Location', to match the
column heading on sheet 2. In the Advanced Filter dialog box, the List should be $A$1:$C$1000, to include all the columns of your table (Locations'!$A$1:$A$7) don't include any blank cells, or all the records will pass through the filter. For the criteria range JCP wrote: and failing miserably :( I've got two sheets in my spreadsheet. Sheet1 has a subset of location numbers in ColA Sheet2 has a lot of columns, one of which is the location number. Name Hours Location Fred 8 2 Joe 10 3 Jim 6 2 I'm trying to use the advanced filter to get Sheet2 to display only those entries which have their location specified in Sheet1. I've taken the List range as being $B$1:$B$1000 (as in example above) and the criteria range as being 'Locations'!$A$1:$A$7 and, as far as I can tell, absolutely nothing happens. Any suggestions as to how I can go about this? Cheers J -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Debra Dalgleish wrote:
On Sheet 1, the column should have the heading 'Location', to match the column heading on sheet 2. In the Advanced Filter dialog box, the List should be $A$1:$C$1000, to include all the columns of your table (Locations'!$A$1:$A$7) don't include any blank cells, or all the records will pass through the filter. For the criteria range JCP wrote: and failing miserably :( I've got two sheets in my spreadsheet. Sheet1 has a subset of location numbers in ColA Sheet2 has a lot of columns, one of which is the location number. Name Hours Location Fred 8 2 Joe 10 3 Jim 6 2 I'm trying to use the advanced filter to get Sheet2 to display only those entries which have their location specified in Sheet1. I've taken the List range as being $B$1:$B$1000 (as in example above) and the criteria range as being 'Locations'!$A$1:$A$7 and, as far as I can tell, absolutely nothing happens. Any suggestions as to how I can go about this? Cheers J Thankyou! I had a blank line after the heading on the first sheet - doh! |
#5
|
|||
|
|||
You're welcome, and thanks for reporting what caused the problem.
Sorry for my scrambled message -- I'm glad you were able to decipher it! For the record, it should have been: '======================================= On Sheet 1, the column should have the heading 'Location', to match the column heading on sheet 2. In the Advanced Filter dialog box, the List should be $A$1:$C$1000, to include all the columns of your table. For the criteria range (Locations'!$A$1:$A$7) don't include any blank cells, or all the records will pass through the filter. '========================================= JCP wrote: Debra Dalgleish wrote: On Sheet 1, the column should have the heading 'Location', to match the column heading on sheet 2. In the Advanced Filter dialog box, the List should be $A$1:$C$1000, to include all the columns of your table (Locations'!$A$1:$A$7) don't include any blank cells, or all the records will pass through the filter. For the criteria range JCP wrote: and failing miserably :( I've got two sheets in my spreadsheet. Sheet1 has a subset of location numbers in ColA Sheet2 has a lot of columns, one of which is the location number. Name Hours Location Fred 8 2 Joe 10 3 Jim 6 2 I'm trying to use the advanced filter to get Sheet2 to display only those entries which have their location specified in Sheet1. I've taken the List range as being $B$1:$B$1000 (as in example above) and the criteria range as being 'Locations'!$A$1:$A$7 and, as far as I can tell, absolutely nothing happens. Any suggestions as to how I can go about this? Cheers J Thankyou! I had a blank line after the heading on the first sheet - doh! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel advanced filter be used in "and NOT" mode | Excel Worksheet Functions | |||
Advanced Filter Problems | Excel Discussion (Misc queries) | |||
Advanced Filter using Date represented as text | Excel Worksheet Functions | |||
Advanced Filter: No Filter | Excel Discussion (Misc queries) | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |