A function for filtering between Dates

[shriil]

have an excel sheet where I keep track of maintenance jobs done
throughout the year. The data looks like as mentioned below:


A1 B1 C1


Descr Job no Job Date


AAA 25/1 12/08/10


BBB 12/2 14/08/10


CCC 30/2 15/08/10


DDD 05/3 02/09/10


.... .... ........


I would like to run a macro or a function where I will filter out the
above range
between two dates, (i.e I input a Start Date and a End Date) and copy
the filtered list to a new sheet.

Ideally the function will be selecting the specified range, say
A1:C100, then ask the user to input a 'start date' and a 'end date',
and finally output the filtered list to another desired range, say
D1:F100.

I normally use the Advanced Filter Option for doing the same but would
like to automate the same for easier user interaction.


Thanks for the help

 

[Max]

Here's a simple formulas set-up which will deliver the desired
automation
Assume source data as posted in Sheet1, cols A to C, data from row 2
down
where the key col = col C (dates)
It's presumed that the dates are all real dates

In Sheet2,
Assume StartDate will be input in A1, EndDate will be input in A2
Put in B2:
=IF(COUNT($A$1:$A$2)<2,"",IF(AND(Sheet1!C2>=$A$1,Sheet1!C2<=$A
$2),ROW(),""))
Put in C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:
$B,ROWS($1:1))))
Copy C2 to E2. Select B2:E2, copy down to cover the max expected
extent of source data, say down to E200? Minimize/hide col B. Cols C
to E will return only the source lines satisfying the date range
specified in A1:A2, all neatly packed at the top. Format to taste.

p/s: To retrieve lines for any single date (instead of a date range),
just input that same date into both A1 & A2

 

[shriil]

Here's a simple formulas set-up which will deliver the desired
automation
Assume source data as posted in Sheet1, cols A to C, data from row 2
down
where the key col = col C (dates)
It's presumed that the dates are all real dates

In Sheet2,
Assume StartDate will be input in A1, EndDate will be input in A2
Put in B2:
=IF(COUNT($A$1:$A$2)<2,"",IF(AND(Sheet1!C2>=$A$1,Sheet1!C2<=$A
$2),ROW(),""))
Put in C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:
$B,ROWS($1:1))))
Copy C2 to E2. Select B2:E2, copy down to cover the max expected
extent of source data, say down to E200? Minimize/hide col B. Cols C
to E will return only the source lines satisfying the date range
specified in A1:A2, all neatly packed at the top. Format to taste.

p/s: To retrieve lines for any single date (instead of a date range),
just input that same date into both A1 & A2














- Show quoted text -

Thanks a lot. Shall try the same

 

[shriil]

Here's a simple formulas set-up which will deliver the desired
automation
Assume source data as posted in Sheet1, cols A to C, data from row 2
down
where the key col = col C (dates)
It's presumed that the dates are all real dates

In Sheet2,
Assume StartDate will be input in A1, EndDate will be input in A2
Put in B2:
=IF(COUNT($A$1:$A$2)<2,"",IF(AND(Sheet1!C2>=$A$1,Sheet1!C2<=$A
$2),ROW(),""))
Put in C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:
$B,ROWS($1:1))))
Copy C2 to E2. Select B2:E2, copy down to cover the max expected
extent of source data, say down to E200? Minimize/hide col B. Cols C
to E will return only the source lines satisfying the date range
specified in A1:A2, all neatly packed at the top. Format to taste.

p/s: To retrieve lines for any single date (instead of a date range),
just input that same date into both A1 & A2














- Show quoted text -

Thanks.. it worked perfectly!

 

[Max]

Thanks.. it worked perfectly!
Glad to hear, you're welcome