Advanced Filters

[Guest]

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

 

[Guest]

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.

 

[Guest]

Blimey, I didn't think it would be that complicated!! Excel and dates just
don't go together, do they?

I'll give it a go.

Thank you.

 

[Guest]

Am I having a blonde moment?? I can't get this to work either, it doesn't
return any data, whereas it should return one record???

Louise

 

[Guest]

when i type this formula into the cell, it automatically turns into 34700,
rather than keeping the actual criteria there??

 

[Roger Govier]

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

 

[Guest]

when i type this formula into the cell, it automatically turns into 34700,
rather than keeping the actual criteria there??

That's fine; Excel's dates (and times) are just specially formatted
numbers. To see for yourself, type the date in some random cell then format
it using the 'comma' style. The challenge is to be able to have the date in
a format you can understand (1/1/05) and Excel can use to compare (34700).
Roger's suggestion to keep the date in a separate cell should work fine, or
you can use the DATE function mentioned earlier.

 

[Guest]

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]

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

 

[Guest]

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