Macro to filter by 3 parameters / Excel 2002 / WinXP

Freewheel · Dec 7, 2005

Hi,

I have a filtering problem that I am unable to solve.

I have a number of source data worksheets, one for each country. Each country datasheet contains invoice data. I then have a statistics worksheet on which I want to pull out various data elements, as filtered by:
a) a drop-down list to select the country
b) entering an "activity period" which limits the invoices to those within two dates.

Here is some sample source data, for the UK worksheet:

Invoices sample.xls

A

B

C

D

E

F

1

Invoice

Activity period

Payment

2

Reference

From

To

Amount invoiced

Amount paid

3

UK-001

01/06/2005

31/08/2005

82,231.20

4

UK-002

01/06/2005

31/08/2005

204,494.03

5

UK-003

01/06/2005

31/08/2005

55,296.00

6

UK-004

04/07/2005

31/08/2005

0.00

7

UK-005

01/06/2005

31/08/2005

148,539.58

8

UK-006

01/06/2005

31/08/2005

401,314.46

9

UK-007

01/06/2005

31/08/2005

179,577.94

10

UK-008

04/07/2005

31/08/2005

0.00

11

UK-009

01/06/2005

31/08/2005

228,321.67

12

UK-010

01/06/2005

31/08/2005

301,150.69

13

UK-011

01/06/2005

31/08/2005

100,462.68

14

UK-012

04/07/2005

31/08/2005

15

United Kingdom

... and here is the Statistics sheet on which I want to produce summarised/filtered data. The country-selector drop-down box is in cell B4; the activity period is selected in cells C4 and D4. I want to pull out the reference number, amount invoiced and amount paid for each matching invoice:

Invoices sample.xls

A

B

C

D

E

1

2

Select country

Select activity period

3

From

To

4

Austria

01/06/2005

31/08/2005

5

6

Invoice ref.

Amount invoiced

Amount Paid

7

8

9

10

11

12

13

14

15

16

17

Statistics

I have been trying to use formulae to achieve this, but to no avail. I set named ranges for each country worksheet, and used a combination of INDEX, OFFSET, INDIRECT and MATCH. The problem is I only get the first exact match, while I want to pull out all matching invoices for a given country/activity period, and have them listed from row 7 downwards. I suspect I need to use some kind of VBA code filter, but I haven't a clue where to start....

With kind regards,
Simon

Andrew Poulsom · Dec 7, 2005

First make sure the headings in B6:D6 on your Statistics sheet exactly match those in your source data worksheets, ie Reference, Amount invoiced and Amount paid.

Then add a button from the Forms Toolbar and assign it this code in a General module:

Code:

Sub GetData()
    Dim Sh As Worksheet
    Dim Rng As Range
    Application.ScreenUpdating = False
    With Worksheets("Statistics")
        Set Sh = Worksheets(.Range("B4").Value)
        Set Rng = Sh.Range("A2:E" & Sh.Range("A65536").End(xlUp).Row)
        Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("C4:D4"), CopyToRange:=.Range("B6:D6"), Unique:=False
        .Range("A1").Select
    End With
    Application.ScreenUpdating = True
End Sub

Freewheel · Dec 8, 2005

Andrew,
That nearly works... it pulls the data out alright, but does not filter it by date. No matter what dates I enter in cells C4:D4 it pulls out all 12 UK records from my sample source data.

Many thanks for the macro anway. I have no coding experience but I can almost see how it works. But I can't work out why it doesn't filter correctly.

Simon

Andrew Poulsom · Dec 8, 2005

Oops

Incorrect criteria range:

Code:

Sub GetData()
    Dim Sh As Worksheet
    Dim Rng As Range
    Application.ScreenUpdating = False
    With Worksheets("Statistics")
        Set Sh = Worksheets(.Range("B4").Value)
        Set Rng = Sh.Range("A2:E" & Sh.Range("A65536").End(xlUp).Row)
        Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("C3:D4"), CopyToRange:=.Range("B6:D6"), Unique:=False
        .Range("A1").Select
    End With
    Application.ScreenUpdating = True
End Sub

Freewheel · Dec 8, 2005

Magic! Thanks Andrew, it works a treat.

At the risk of pushing my luck, how can I get the macro to pull out invoices which have dates within the date filter range (i.e. not just an exact date match)?

E.g. filter range is 01/01/2005 to 31/12/2005
If there was an invoice with activity period 01/05/05 to 01/07/05 I would like to retrieve this too.

Many thanks, this certainly helps me to get my head around how to write code!

Simon

Andrew Poulsom · Dec 8, 2005

To do that you would have to construct a criteria range from the dates entered. On your Statistics sheet copy C3:D3 to E3. In E4 enter:

=">="&C4

and in F4 enter:

="<="&D4.

Now try this code:

Code:

Sub GetData()
    Dim Sh As Worksheet
    Dim Rng As Range
    Application.ScreenUpdating = False
    With Worksheets("Statistics")
        Set Sh = Worksheets(.Range("B4").Value)
        Set Rng = Sh.Range("A2:E" & Sh.Range("A65536").End(xlUp).Row)
        Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("E3:F4"), CopyToRange:=.Range("B6:D6"), Unique:=False
        .Range("A1").Select
    End With
    Application.ScreenUpdating = True
End Sub

Freewheel · Dec 8, 2005

Again that works a treat. Many thanks!

Just to complicate matters, what I posted was sample data. I am trying to get it to work on my real (confidential) data. In my real data sheet I have more columns than the five that I posted, as follows:

Invoice ref. is still in column A
Activity period is in columns F & G
Amount invoiced is in column M
Amount paid is in column N

My stats sheet is the same as posted.

So I tweaked the range specified in line 7 of your code:

Set Rng = Sh.Range("A2:N" & Sh.Range("A65536").End(xlUp).Row)

Now I get "run time error 1004: the extract range has a missing or illegal file name" and the debugger highlights this line:

Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("E3:F4"), CopyToRange:=.Range("B7:D7"), Unique:=False

Any ideas?

Freewheel · Dec 8, 2005

that error message should have read "missing or illegal field name", not "file name".

Andrew Poulsom · Dec 8, 2005

What are the headings in A2:N2?

Freewheel · Dec 8, 2005

A - Unique ID
B - NAA Reference
C - Issue date
D - Receipt date
E - Service Type
F - From
G - To
H - Reference
I - Max amount
J - Date
K - By
L - Working hours
M - Amount invoiced
N - Amount paid

Some of these are in font colour white. There are main headings above in Row 1.

Headings in my Statistics worksheet are now:
B - Unique ID
C - Amount invoiced
D - Amount paid

I pasted these heading names from the other sheet to ensure they are the same.

Macro to filter by 3 parameters / Excel 2002 / WinXP

Freewheel

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Freewheel

New Member

Andrew Poulsom

MrExcel MVP

Freewheel

New Member

Andrew Poulsom

MrExcel MVP

Freewheel

New Member

Freewheel

New Member

Andrew Poulsom

MrExcel MVP

Freewheel

New Member

Similar threads

Forum statistics

Share this page

Macro to filter by 3 parameters / Excel 2002 / WinXP

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock