Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses, phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results depending on
the input. Access might be a better choice to use for long lists, but I
thought that if I were using Excel, I could possibly use an Advanced Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using the
data I want to filter, and for criteria, I've highlighted the "criteria" area.

What am I doing wrong? I would appreciate any help I could get. Thanks in
advance.

- Jonathan

What's your criteria?

--


Regards,


Peo Sjoblom

"Jon Lam" <Jon wrote in message
...
Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses, phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results depending
on
the input. Access might be a better choice to use for long lists, but I
thought that if I were using Excel, I could possibly use an Advanced
Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run
the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using the
data I want to filter, and for criteria, I've highlighted the "criteria"
area.

What am I doing wrong? I would appreciate any help I could get. Thanks
in
advance.

- Jonathan

Hi Peo,

Thanks for your reply. I'm a bit uncertain as to what your question is
pertaining to, but I'll try to answer it correctly. My criteria is the area
that was set up to type in the search values. So the headers on the criteria
table are the same as those on the data list. Then I put three spaces
between the header of the criteria area and the headers of the data list, as
suggested by the Excel Help window.

If your question was regarding the input that I was putting in, then it
varies a bit. I was trying to have it set up so I could filter by name for
instance, or by the area in which they work.

If criteria, you meant function, then I wanted to create an excel database
so that if I want to look for a specific person, or for people within a
specific region, then I could do so by an advanced filter. I could use an
autofilter, but I wanted to copy the data onto a separate worksheet as well
because too much data in one place would clutter the screen.

I hope one of these explanations hit upon your question.

"Peo Sjoblom" wrote:

What's your criteria?

--


Regards,


Peo Sjoblom

"Jon Lam" <Jon wrote in message
...
Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses, phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results depending
on
the input. Access might be a better choice to use for long lists, but I
thought that if I were using Excel, I could possibly use an Advanced
Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run
the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using the
data I want to filter, and for criteria, I've highlighted the "criteria"
area.

What am I doing wrong? I would appreciate any help I could get. Thanks
in
advance.

- Jonathan

If your header for let's say sales people is named SalesPeople and you want
to filter a certain name
let's say Johnson, and you have 2 other headers and the table is called
MyTable
you would for example put SalesPeople in H1, the second header in I1 and the
third in J1
then you would put Johnson in H2 and leave I2:J2 blank

So when you set up your filter your criteria range would be $H$1:$J$2, your
list range would be the actual cells for MyTable including the headers.
having the criteria range set up this way is the equivalent of the AND
function meaning if
you put in a second criteria in I2 let's say number 1 it means the filter
looks for JOHNSON
where the number column is 1. If you want an OR situation you would put the
criteria 1
in I3 and leave I2 blank then you would need to extend the criteria range to
$H$1:$J$3





--


Regards,


Peo Sjoblom

"Jon Lam" wrote in message
...
Hi Peo,

Thanks for your reply. I'm a bit uncertain as to what your question is
pertaining to, but I'll try to answer it correctly. My criteria is the
area
that was set up to type in the search values. So the headers on the
criteria
table are the same as those on the data list. Then I put three spaces
between the header of the criteria area and the headers of the data list,
as
suggested by the Excel Help window.

If your question was regarding the input that I was putting in, then it
varies a bit. I was trying to have it set up so I could filter by name
for
instance, or by the area in which they work.

If criteria, you meant function, then I wanted to create an excel database
so that if I want to look for a specific person, or for people within a
specific region, then I could do so by an advanced filter. I could use an
autofilter, but I wanted to copy the data onto a separate worksheet as
well
because too much data in one place would clutter the screen.

I hope one of these explanations hit upon your question.

"Peo Sjoblom" wrote:

What's your criteria?

--


Regards,


Peo Sjoblom

"Jon Lam" <Jon wrote in message
...
Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses,
phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results
depending
on
the input. Access might be a better choice to use for long lists, but
I
thought that if I were using Excel, I could possibly use an Advanced
Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run
the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using
the
data I want to filter, and for criteria, I've highlighted the
"criteria"
area.

What am I doing wrong? I would appreciate any help I could get.
Thanks
in
advance.

- Jonathan

I have frequently made the mistake of defining the criteria range to include
the field name and maybe two cells below that. The problem is that the two
cells must both have the filter value or everything will show. Better,
define the criteria range to include just the cell with the field name and
one cell below it then put the filter value in that one cell.

In other words, you cannot have blank cells in the criteria range.

Tom

"Jon Lam" wrote:

Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses, phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results depending on
the input. Access might be a better choice to use for long lists, but I
thought that if I were using Excel, I could possibly use an Advanced Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using the
data I want to filter, and for criteria, I've highlighted the "criteria" area.

What am I doing wrong? I would appreciate any help I could get. Thanks in
advance.

- Jonathan

thank you tom, that simple one liner at the end of your message really helped!



TomP wrote:

I have frequently made the mistake of defining the criteria range to include
22-Aug-08

I have frequently made the mistake of defining the criteria range to include
the field name and maybe two cells below that. The problem is that the two
cells must both have the filter value or everything will show. Better,
define the criteria range to include just the cell with the field name and
one cell below it then put the filter value in that one cell.

In other words, you cannot have blank cells in the criteria range.

Tom

"Jon Lam" wrote:

Previous Posts In This Thread:

On Friday, August 22, 2008 3:15 PM
Jon La wrote:

My Advance Filter Doesn't Filter
Hi Everyone,

I'm currently using Excel 2002. I have a list of names, addresses, phone
numbers and other info. I was trying to make it so that when I input a
search criteria, I could get back a result or a list of results depending on
the input. Access might be a better choice to use for long lists, but I
thought that if I were using Excel, I could possibly use an Advanced Filter
to do something like that.

I have the criterion space, the list, and the place I want to copy the
results to set up as the office help stated, but everytime I try to run the
filter, it doesn't seem to work. If I did the "copy to a new location"
option, it ends up copying the entire list over. For list, I'm using the
data I want to filter, and for criteria, I've highlighted the "criteria" area.

What am I doing wrong? I would appreciate any help I could get. Thanks in
advance.

- Jonathan

On Friday, August 22, 2008 3:46 PM
Peo Sjoblom wrote:

What's your criteria?
What's your criteria?

--


Regards,


Peo Sjoblom

On Friday, August 22, 2008 4:15 PM
JonLa wrote:

Hi Peo,Thanks for your reply.
Hi Peo,

Thanks for your reply. I'm a bit uncertain as to what your question is
pertaining to, but I'll try to answer it correctly. My criteria is the area
that was set up to type in the search values. So the headers on the criteria
table are the same as those on the data list. Then I put three spaces
between the header of the criteria area and the headers of the data list, as
suggested by the Excel Help window.

If your question was regarding the input that I was putting in, then it
varies a bit. I was trying to have it set up so I could filter by name for
instance, or by the area in which they work.

If criteria, you meant function, then I wanted to create an excel database
so that if I want to look for a specific person, or for people within a
specific region, then I could do so by an advanced filter. I could use an
autofilter, but I wanted to copy the data onto a separate worksheet as well
because too much data in one place would clutter the screen.

I hope one of these explanations hit upon your question.

"Peo Sjoblom" wrote:

On Friday, August 22, 2008 5:37 PM
Peo Sjoblom wrote:

If your header for let's say sales people is named SalesPeople and you want to
If your header for let's say sales people is named SalesPeople and you want
to filter a certain name
let's say Johnson, and you have 2 other headers and the table is called
MyTable
you would for example put SalesPeople in H1, the second header in I1 and the
third in J1
then you would put Johnson in H2 and leave I2:J2 blank

So when you set up your filter your criteria range would be $H$1:$J$2, your
list range would be the actual cells for MyTable including the headers.
having the criteria range set up this way is the equivalent of the AND
function meaning if
you put in a second criteria in I2 let's say number 1 it means the filter
looks for JOHNSON
where the number column is 1. If you want an OR situation you would put the
criteria 1
in I3 and leave I2 blank then you would need to extend the criteria range to
$H$1:$J$3





--


Regards,


Peo Sjoblom

"Jon Lam" wrote in message
...

On Friday, August 22, 2008 6:17 PM
TomP wrote:

I have frequently made the mistake of defining the criteria range to include
I have frequently made the mistake of defining the criteria range to include
the field name and maybe two cells below that. The problem is that the two
cells must both have the filter value or everything will show. Better,
define the criteria range to include just the cell with the field name and
one cell below it then put the filter value in that one cell.

In other words, you cannot have blank cells in the criteria range.

Tom

"Jon Lam" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Join Lists with LINQ - SharePoint 2010
http://www.eggheadcafe.com/tutorials...-linq--sh.aspx