Hlp! Named range Advanced Filter Criterion

[Joyce]

Hello,

I'm trying to use a named range for my criterion in an advanced filter, to
no avail.

Here is what I'd like to do:

I have a single cell that is named SpecDate. On another worksheet, I have
an advanced filter where I would like to specify >SpecDate. If I key the
date in manually no problem. But the moment I use a named range, it doesn't
work.

Are named ranges allowable in Adv Filter criteria?

Thanks!

 

[JMay]

Try

=">" & SelectDate

 

[Joyce]

Unfortunately, that didn't work either. I'm stumped.

 

[JMay]

=">" & SpecDate << Sorry, Used wrong name, try this one (yours)

 

[Joyce]

I did replace your name with mine when I tried it, but it still didn't work.

 

[Dave Peterson]

I usually include at least 2 cells--the top one to match the header in the table
and the second one to be the criteria for that matching field.

But I know you can do it:
http://contextures.com/xladvfilter02.html
(From Debra Dalgleish's site)

Maybe you could explain more about how your data is laid out and what you're
filtering by.

 

[Joyce]

Hi Dave,

This is the layout of my data:

Client Received on Rep

SpecDate Jane

Client Received on Rep
ABC 03-Jul-09 John
DEF 17-Nov-09 Jane
GHI 04-Nov-09 Joe
JKL 15-Oct-09 Jane

SpecDate is a single cell which resides on another sheet within the same
workbook. I want to filter by date > than SpecDate with the Rep specified as
well.

Thanks.

 

[Dave Peterson]

I put your data (and a little more) in Shee1 A1:C6

Client Received on Rep
ABC 07/03/2009 John
DEF 11/17/2009 Jane
GHI 11/04/2009 Joe
JKL 10/15/2009 Jane
DEF 12/02/2010 Jane

And I put the criteria in A1:C2 of sheet2:
Client Received on Rep

40118 Jane

The formula I used in B2 was:
=">"&date(2009,11,1)
But I could have entered:

40118

(which is what I see in that cell)

Then I used Data|Filter|Advanced filter.

I specified the data on Sheet1 (a1:c6) and the criteria on sheet2 (a1:c2) and
the results were the two records (rows 3 and 6) that I expected to see.

This is the code that I got when I recorded that action:

Option Explicit
Sub Macro1()
Range("A1:C6").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Sheet2").Range("A1:C2"), _
Unique:=False
End Sub

I wouldn't use this as-is. I'd want to qualify the ranges:

Option Explicit
Sub Macro1()
Worksheets("sheet1").Range("A1:C6").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Worksheets("Sheet2").Range("A1:C2"), _
Unique:=False
End Sub

And if B2 were the cell (named SpecDate) where I'd want to specify the date, I'd
use something like:

Option Explicit
Sub Macro1()

With Worksheets("Sheet2")
.Range("SpecDate").Value = ">" & CLng(DateSerial(2009, 11, 1))
Worksheets("sheet1").Range("A1:C6").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A1:C2"), _
Unique:=False
End With
End Sub

Actually, with my date settings (mm/dd/yyyy as the USA short date format), this
worked ok:

.Range("SpecDate").Value = ">" & DateSerial(2009, 11, 1)

But I think that I'd use clng() to avoid any date format/international issues.

 

[Joyce]

Hi Dave,

Thanks very much for your response.

I was hoping to avoid using VBA.

I guess I just don't understand why I can use >Jan 1, 09 and it works fine,
but I can't seem to achieve the same thing with a named cell, or even cell
address. I don't want to have to actually type the date in each time - I'd
just like to be able to reapply the same filter over and over, with date
criteria being updated.

I've been working on this all afternoon. Is it impossible to use cell
references or named ranges in advanced filter criteria?

Hmmm...

 

[Dave Peterson]

Where do you plan on putting the named cell?

And how are you planning on using it when you do it manually.

I described how I did the advanced filter through the user interface.

Can you do the same thing? Step by step.

 

[Joyce]

Hi Dave,

Your bit with the & before the formula in the advanced filter criterion
helped to resolve this in a simple way. Here is the formula I ended up with:

=">"&INDEX(SpecDate,1,1)

It worked perfectly!

Thanks!!