Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Advanced Filters
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 |
#2
|
|||
|
|||
Advanced Filters
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 |
#3
|
|||
|
|||
Advanced Filters
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. "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 |
#4
|
|||
|
|||
Advanced Filters
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 "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 |
#5
|
|||
|
|||
Advanced Filters
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 |
#6
|
|||
|
|||
Advanced Filters
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 |
#7
|
|||
|
|||
Advanced Filters
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. |
#8
|
|||
|
|||
Advanced Filters
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 |
#9
|
|||
|
|||
Advanced Filters
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced filters | Excel Worksheet Functions | |||
Help: Multiple filters with mutually exclusive items | Excel Discussion (Misc queries) | |||
Req: How to create multiple mutually exclusive filters | Excel Worksheet Functions | |||
Advanced Filters | Excel Worksheet Functions | |||
How to aviod reset of Pivot filters when reconnecting to database? | Excel Discussion (Misc queries) |