View Full Version : autofilter with a cell as the criteria
wibbers2000
11-17-2005, 05:58 AM
Hi
I would like to be able to use an custom auto filter using the information from a static cell as the criteria.
Example; column a = order number
b = description
c = date (dd/mm/yy)
cell d1 will have a date - will be changed often
i would like to autofilter the date column 'C' but using the data in cell D1 as the filter criteria
Ideally I would like to use the <= value in D1
Any help will be appreciated
regards
Bob Phillips
11-17-2005, 06:11 AM
Hi
I would like to be able to use an custom auto filter using the information from a static cell as the criteria.
Example; column a = order number
b = description
c = date (dd/mm/yy)
cell d1 will have a date - will be changed often
i would like to autofilter the date column 'C' but using the data in cell D1 as the filter criteria
Ideally I would like to use the <= value in D1
Any help will be appreciated
regards
If you mean without VBA, then take a look at Data>Filter>Advanced Filter.
wibbers2000
11-17-2005, 06:18 AM
I am unable to get the filter to use the cell D1 for reference, so i think it would have to be done via VBA.
However, I would love to be proved wrong as filters are new to me.
regards
Wolfgang
11-17-2005, 06:21 AM
hi...
autofilter as opposed to advancedfilter doe not allow an "external" reference...
you may generate some "custom views" and store you criteria there...
then you will be able to edit them as you like and use them as you like without having to switch between "show all" and your new criteria..
best,
wolfgang
wibbers2000
11-17-2005, 06:41 AM
Sorry Wolfgang
I am still unable to do what I want...
1. If it use auto filter, I can then select the drop down arrow and choose custom. I the select the <= XXXX (XXXX being the date). This will display the rows I want
2. If I use advance filter, I get nothing... I cant get it to use a cell value for the filter criteria.
What I want to do is just as point one, but without have to go through all the steps. I would like the criteria to be entered in cell D1 and then the macro/autofilter/VBA to use that value.
I just want to put a button on the page and link it to a macro etc rather than have the users try and figure out filters
thanks for your advice anyway I will be looking into advanced filters in more depth.
regards
Bob Phillips
11-17-2005, 07:05 AM
Sorry Wolfgang
I am still unable to do what I want...
1. If it use auto filter, I can then select the drop down arrow and choose custom. I the select the <= XXXX (XXXX being the date). This will display the rows I want
2. If I use advance filter, I get nothing... I cant get it to use a cell value for the filter criteria.
What I want to do is just as point one, but without have to go through all the steps. I would like the criteria to be entered in cell D1 and then the macro/autofilter/VBA to use that value.
I just want to put a button on the page and link it to a macro etc rather than have the users try and figure out filters
thanks for your advice anyway I will be looking into advanced filters in more depth.
regards
In advanced filter, put the column heading in a cell, link to that date cell in the cell below, and define both as the criteria.
Wolfgang
11-17-2005, 07:07 AM
don't give up so early..........
i attach an example workbook, if i am able to do so....
then please have a look and good luck...
best,
wolfgang
wibbers2000
11-17-2005, 07:12 AM
thanks XLD
I got the advance filter to work.
Is there a way to filter bewteen a range of dates or <= to date.
--I am beginning to think that I may need to look at a loop process to search the rows for date <= and the copy that row and paste into a new sheet and then repeat the loop until all rows have been checked.
regards
wibbers
dragon-it
11-20-2005, 12:05 AM
Can't you use the advanced filter to do the date range too:
e.g.
D1 Start Date
E1 End Date
F1 blank
F2 =and(c2 >=$d$1, C2 <=$e$1)
where C2 = first entry in date column
Then setup the advanced filter using $F$1:$F$2 as the criteria range.
You can refresh the filter using a button running a macro or when the sheet changes etc. using something like:
Range("DataRangeName").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range("Criteria")
Steve
wibbers2000
11-20-2005, 03:43 AM
Can't you use the advanced filter to do the date range too:
e.g.
D1 Start Date
E1 End Date
F1 blank
F2 =and(c2 >=$d$1, C2 <=$e$1)
where C2 = first entry in date column
Then setup the advanced filter using $F$1:$F$2 as the criteria range.
You can refresh the filter using a button running a macro or when the sheet changes etc. using something like:
Range("DataRangeName").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range("Criteria")
Steve
thanks Steve
that is perfect... just going to add a couple of input boxes to update the date range cells and it will do just as requested.:bow:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.