Autofilter help- doesn't allow formulas in its criteria?

Yes, this is almost identical to my earlier post about "SUMIF" not allowing formulas. Everyone was so immensely helpful, I cannot help but ask another question. (Thanks again, everyone)

For my SUMIF question, I was told that SUMIF DOES allow formulas as long as they are concatenated with the operator, AND the operator was in quotes (e.g. "="&Sheet2!B2). This works great!

Now I am trying to use the autofilter to call the value from a cell. It's the same cell that my SUMIF function references: Sheet2!B2.

If I just make a custom autofilter through Excel, I can select "less than or equal to" and then must type in a value. This seems eerily familiar to the SUMIF function- I can't believe that excel won't let me filter by the value in a given cell.

For example, I have a data table with, among other things, sales and Date Sold. I want to view all items sold beyond 30 days ago. I crudely do this by having the B2 cell use the function =TODAY()-30, which returns exactly what I want. I then reference cell B2 in any formulas that require it.

So I then checked in VB the formula for autofilter, and it looks similar to the SUMIF function:

Selection.AutoFilter Field:=13, Criteria1:="=6/28/2004", Operator:=xlAnd

I reason by analogy that by changing Criteria1 to "="&Sheet2!B2 it should work. It does not. The filter removes EVERYTHING, and I am left with a blank table, minus my headers. I find it difficult to accept that Excel will only allow me to manually type in a value for a Filter. Is there any way, even if it is a workaround solution, that I can accomplish this? The purpose is to automate this into a macro so that I can run this report at will and not have to filter by this date each time.

Thanks so much in advance!

Hi
AFAIK no chance to include cell references in AutoFilter. One
workaround: Use Advanced filters

--
Regards
Frank Kabel
Frankfurt, Germany


JAnderson wrote:
Yes, this is almost identical to my earlier post about "SUMIF" not
allowing formulas. Everyone was so immensely helpful, I cannot help
but ask another question. (Thanks again, everyone)

For my SUMIF question, I was told that SUMIF DOES allow formulas as
long as they are concatenated with the operator, AND the operator was
in quotes (e.g. "="&Sheet2!B2). This works great!

Now I am trying to use the autofilter to call the value from a cell.
It's the same cell that my SUMIF function references: Sheet2!B2.

If I just make a custom autofilter through Excel, I can select "less
than or equal to" and then must type in a value. This seems eerily
familiar to the SUMIF function- I can't believe that excel won't let
me filter by the value in a given cell.

For example, I have a data table with, among other things, sales and
Date Sold. I want to view all items sold beyond 30 days ago. I
crudely do this by having the B2 cell use the function =TODAY()-30,
which returns exactly what I want. I then reference cell B2 in any
formulas that require it.

So I then checked in VB the formula for autofilter, and it looks
similar to the SUMIF function:

Selection.AutoFilter Field:=13, Criteria1:="=6/28/2004",
Operator:=xlAnd

I reason by analogy that by changing Criteria1 to "="&Sheet2!B2 it
should work. It does not. The filter removes EVERYTHING, and I am
left with a blank table, minus my headers. I find it difficult to
accept that Excel will only allow me to manually type in a value for
a Filter. Is there any way, even if it is a workaround solution,
that I can accomplish this? The purpose is to automate this into a
macro so that I can run this report at will and not have to filter by
this date each time.

Thanks so much in advance!

In VBA you can set a variable equal to the current value of cell B2 and
then filter by the variable name.


Code:
--------------------


vCriteria = "=" & Sheet2.Range("B2")

Selection.AutoFilter Field:=13, Criteria1:=vCriteria, Operator:=xlAnd
--------------------




Rollin


---
Message posted from http://www.ExcelForum.com/

Thanks for the info, but I must admit, I'm just a hair above novice at VB. Is VBA the Excel editor? How do I use this code- do I have to dimension the variable first? Do I just paste it into my macro before the filter step? Thanks!!!!

Jason

"Rollin_Again " wrote:

In VBA you can set a variable equal to the current value of cell B2 and
then filter by the variable name.


Code:
--------------------


vCriteria = "=" & Sheet2.Range("B2")

Selection.AutoFilter Field:=13, Criteria1:=vCriteria, Operator:=xlAnd
--------------------




Rollin


---
Message posted from http://www.ExcelForum.com/

Thanks for the info, but I must admit, I'm just a hair above novice at VB. Is VBA the Excel editor? How do I use this code- do I have to dimension the variable first? Do I just paste it into my macro before the filter step? Thanks!!!!

Jason


"Rollin_Again " wrote:

In VBA you can set a variable equal to the current value of cell B2 and
then filter by the variable name.


Code:
--------------------


vCriteria = "=" & Sheet2.Range("B2")

Selection.AutoFilter Field:=13, Criteria1:=vCriteria, Operator:=xlAnd
--------------------




Rollin


---
Message posted from http://www.ExcelForum.com/

Thanks for the info, but I must admit, I'm just a hair above novice at VB. Is VBA the Excel editor? How do I use this code- do I have to dimension the variable first? Do I just paste it into my macro before the filter step? Thanks!!!!

Jason

"Rollin_Again " wrote:

In VBA you can set a variable equal to the current value of cell B2 and
then filter by the variable name.


Code:
--------------------


vCriteria = "=" & Sheet2.Range("B2")

Selection.AutoFilter Field:=13, Criteria1:=vCriteria, Operator:=xlAnd
--------------------




Rollin


---
Message posted from http://www.ExcelForum.com/

You don't have to use a variable at all if you don't want. Look at your
macro code. The line of code that is setting the autofilter is

SELECTION.AUTOFILTER FIELD:=13, CRITERIA1:=\"=6/28/2004\",
OPERATOR:=XLAND


Just change it so that it appears as


SELECTION.AUTOFILTER FIELD:=13, CRITERIA1:=\"=\" &
SHEET2.RANGE(\"B2\"), OPERATOR:=XLAND





Rollin


---
Message posted from http://www.ExcelForum.com/

Wow- that works like a charm. Thank you! I'm not sure where you got the forward slashes, but when I took them out, VB recognized it and it worked! (different verison maybe?)

So it looks like my problem was that I was using Sheet2!B2 when I should have been using Sheet2.Range("B2").

I cannot thank this discussion group enough for its collective wisdom. Sorry about the duplicate posts; the site was giving me errors and said that it would not be posted. Thanks again,

Jason

"Rollin_Again " wrote:

You don't have to use a variable at all if you don't want. Look at your
macro code. The line of code that is setting the autofilter is

SELECTION.AUTOFILTER FIELD:=13, CRITERIA1:=\"=6/28/2004\",
OPERATOR:=XLAND


Just change it so that it appears as


SELECTION.AUTOFILTER FIELD:=13, CRITERIA1:=\"=\" &
SHEET2.RANGE(\"B2\"), OPERATOR:=XLAND





Rollin


---
Message posted from http://www.ExcelForum.com/