Advanced Filters
Hi Roger
Thanks very much for all your help, it's really appreciated.
I think I've found a way around it.........
My worksheet has staff names in column A and Department in Column D. I only
want to return, on a separate worksheet, the names of the staff who work in
the Sales or Finanance Department, therefore excluding B and C.
When I enter the criteria on the separate worksheet, ie:
Name Department
Sales
Finance
it gives me the information in Columns B and C as well. However, I have
also type the words 'Name' and 'Department' into the cell where the
information is being entered into. Doing it this way, in the dialog box when
I have to enter where the information is being copied to, if I select the
cells containing the words 'name' and 'department', it only gives me the info
from those two columns!
Sorry this has all been so long-winded, I have only ever used the AutoFilter
function before now.
Any more tips you have would be appreciated though!
Have a good weekend.
Louise
"Roger Govier" wrote:
Hi Louise
Advanced Filter brings across complete rows of data from the source table
that match the criteria set for the filters in place.
That means, you have to have columns B and C brought across, not just
columns A and D.
The easy way around this, is to just Hide the columns you don't want to show.
Record a macro to invoke the Filter, and hide the relevant columns. Then
just run the macro each time.
If you are having further problems, post back or send me directly a copy of
your workbook with what you are trying to achieve and I will try to sort it
out for you. To email me direct, remove NOSPAM from my address.
I will be going out in an hours time, and won't get back till evening, but I
can look at it then.
Regards
Roger Govier
Louise wrote:
Roger
Thank you for your help, I'll give that a try.
Whilst on the subject of filters, are you allowed to perform an advanced
filter where your list range consists of two columns from a main table that
are not next to each other?
For instance, I have names in column A and a membership number in Column D,
with other data in B and C. I have tried to perform a filter that will just
put the people's names and membership number on a separate worksheet but it
keeps saying the list is invalid. Is that why?
Thanks again.
Louise
"Roger Govier" wrote:
Hi Louise
When I use dates in Advance Filter, I tend to put the date in a cell outside
of my filter Criteria range.
Then in the criteria cell I put
="="&C8 where C8 holds the date as 27/10/2005
It shows up as =38652
Regards
Roger Govier
Louise wrote:
when i type this formula into the cell, it automatically turns into 34700,
rather than keeping the actual criteria there??
"bpeltzer" wrote:
You'll have to help Excel understand the 1/1/05. Change the date criterion
to =" "& DATE(2005,1,1). With the DATE function, you can understand and
change it, and Excel can convert it to a number for its use in the filter.
"Louise" wrote:
I have a worksheet containing various data, including staff names, their
start date and the department they work for.
I am trying to use an Advanced Filter which will give me a list on another
worksheet of those staff who started after a particular date but, for some
reason, I can't get it to work.
My 'criteria' looks like:-
Name Start Date
01/01/05
I have made sure that al the cells containing dates are formatted in the
same way but it still doesn't work.
I have created quite a few filters and they have all worked fine, apart from
when I try to use dates.
Any ideas what I'm doing wrong???
Thank you.
Louise
|