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
To
:
Previous Posts In This Thread:
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
What's your criteria?
What's your criteria
-
Regards
Peo Sjoblom
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
:
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 nam
let's say Johnson, and you have 2 other headers and the table is called
MyTabl
you would for example put SalesPeople in H1, the second header in I1 and the
third in J
then you would put Johnson in H2 and leave I2:J2 blan
So when you set up your filter your criteria range would be $H$1:$J$2, you
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 i
you put in a second criteria in I2 let's say number 1 it means the filter
looks for JOHNSO
where the number column is 1. If you want an OR situation you would put the
criteria
in I3 and leave I2 blank then you would need to extend the criteria range t
$H$1:$J$
--
Regards
Peo Sjoblo
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
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Join Lists with LINQ - SharePoint 2010
http://www.eggheadcafe.com/tutorial...6e-7d3fb7d38eca/join-lists-with-linq--sh.aspx