autofilter with a cell as the criteria

[wibbers2000]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.