Thread: return data which occurred in a period

Hi There.

I'm trying to find a way to return data which occurred in the past period.

For example, i have the following data set up as follows.

A B C
1 1/2/02 Purchase of pants -$15,000
2 12/2/02 Sale of shoes $4,000
3 31/3/02 Sale of xxx $2,400
4 13/5/02 Purchase of ties -$2,000

etc etc

I'm looking for a lookup function of some sort which can look at the above data and a return all the transactions that occurred in the month of February for example. I don't want to use an autofilter as I'm drawing the data from various spreadsheets.....

I hope someone can help.

Cheers

Easty

Say you have that data in Sheet1 and you enter an input date of Feb-1, 2002 to represent month of February in B1 of Sheet2, then, in Sheet1 add a column in D2, with formula:

=IF(MONTH(A2)=MONTH(Sheet2!$B$1),COUNT(D$1:D1)+1,"")

copied down to assign numerical sequence to matching months

then in E2 enter: =MAX(D:D) to get number of matching records.

In Sheet2, A2 enter:

=IF(ROWS($B$1:$B1)>Sheet1!$E$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($B$1:$B1),Sheet1!$D:$D)))

copied down as far as you need and across 2 more columns.