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

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
.

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

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!

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