Trying to use Advanced Filter

  • Thread starter Thread starter JCP
  • Start date Start date
J

JCP

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
 
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
 
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
 
Debra said:
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
Click to expand...
Thankyou!
I had a blank line after the heading on the first sheet - doh!
 
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.
'=========================================
 
Back
Top