Macro to filter by 3 parameters / Excel 2002 / WinXP

[Freewheel]

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	82,231.20
4	UK-002	01/06/2005	31/08/2005	204,494.03	204,494.03
5	UK-003	01/06/2005	31/08/2005	55,296.00	55,296.00
6	UK-004	04/07/2005	31/08/2005	0.00	0.00
7	UK-005	01/06/2005	31/08/2005	148,539.58	148,539.58
8	UK-006	01/06/2005	31/08/2005	401,314.46	401,314.46
9	UK-007	01/06/2005	31/08/2005	179,577.94	179,577.94
10	UK-008	04/07/2005	31/08/2005	0.00	0.00
11	UK-009	01/06/2005	31/08/2005	228,321.67	228,321.67
12	UK-010	01/06/2005	31/08/2005	301,150.69	301,150.69
13	UK-011	01/06/2005	31/08/2005	100,462.68	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]

I can't reproduce the error with those headings if I filter the list manually. What happens if you try it on the chosen sheet?

 

[Freewheel]

If I manually apply a custom autofilter to the United Kingdom sheet, and set:
- dates greater than 1/06/05 in the From field
- dates less than 1/08/05 in the To field

...it works fine. Is that what you meant?

Would it help if I sent you the file?

 

[Freewheel]

I've figured it out
Just a stupid mistake that came from too long staring at the screen.

Many thanks for your help Andrew!