Here's what I would like to do, starting with the following data:

Date Year Amount Description
25/04/04 2005 25.65 Aaaa
26/04/04 2004 32.25 Bbbb
27/04/04 2004 48.54 Cccc
28/04/04 2005 63.24 Dddd
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
03/01/05 2005 48.57 Gggg
04/01/05 2004 73.50 Hhhh

I have a table with 4 columns: Date the payment is made, Year for wich the
payment is made, Amount and Description. Each columns is associated with a
dynamic named range (AllDates, AllYears, AllAmounts, AllDesc).

On a different worksheet, I would like to automatically list the rows in
which a payment is made for a different year, with separate lists for
payments for 2004 made in 2005 and for payments for 2005 made in 2004.
Something like that:

For 2004 (payments made in 2005):
Date Year Amount Description
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
04/01/05 2004 73.50 Hhhh

For 2005 (payments made in 2004):
Date Year Amount Description
25/04/04 2005 25.65 Aaaa
28/04/04 2005 63.24 Dddd

I'm guessing that I need some kind of array formula, but can't really find a
way to do it...
The only thing I could come up with is
{ =IF(AND(YEAR(AllDates)=2004;AllYears=2005);AllDate s;"") }
but it doesn't return anything (no errors either), and even if it did I
would have empty rows inbetween the rows I want.

I also looked at Pivot Tables, but couldn't really put my mind around
that...

So, is it possible to do this with worksheet functions? Or do I have to go
the VBA way, and generate my lists 'manually'?

I'm usually pretty good finding stuff with Google, but I have a hard time
finding appropriate search words for this...

Thank you!

Bruno G.

Try the following formula-based approach...

Assuming that Sheet1 contains your source table, enter the following on
Sheet2...

A1: enter 'Year', which is just a label

B1: enter 'Date Paid', which is just a label

A2: enter your first criteria (Year), for example 2004

B2: enter your second criteria (Date Paid), for example 2005

C1: enter a 0 (zero)

C2: copied down:

=IF((Sheet1!A2<"")*(YEAR(Sheet1!A2)=$B$2)*(Sheet1 !B2=$A$2),LOOKUP(9.9999
9999999999E+307,$C$1:C1)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

E2, copied down:

=IF(ROW()-ROW($E$2)+1<=$D$1,MATCH(ROW()-ROW($E$2)+1,C:C,0),"")

F2, copied down and across:

=IF(N($E2),INDEX(Sheet1!A:A,$E2),"")

Now, change your criteria for A2 and B2 to return a list of the
corresponding information.

Hope this helps!

In article ,
"Bruno G." wrote:

Here's what I would like to do, starting with the following data:

Date Year Amount Description
25/04/04 2005 25.65 Aaaa
26/04/04 2004 32.25 Bbbb
27/04/04 2004 48.54 Cccc
28/04/04 2005 63.24 Dddd
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
03/01/05 2005 48.57 Gggg
04/01/05 2004 73.50 Hhhh

I have a table with 4 columns: Date the payment is made, Year for wich the
payment is made, Amount and Description. Each columns is associated with a
dynamic named range (AllDates, AllYears, AllAmounts, AllDesc).

On a different worksheet, I would like to automatically list the rows in
which a payment is made for a different year, with separate lists for
payments for 2004 made in 2005 and for payments for 2005 made in 2004.
Something like that:

For 2004 (payments made in 2005):
Date Year Amount Description
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
04/01/05 2004 73.50 Hhhh

For 2005 (payments made in 2004):
Date Year Amount Description
25/04/04 2005 25.65 Aaaa
28/04/04 2005 63.24 Dddd

I'm guessing that I need some kind of array formula, but can't really find a
way to do it...
The only thing I could come up with is
{ =IF(AND(YEAR(AllDates)=2004;AllYears=2005);AllDate s;"") }
but it doesn't return anything (no errors either), and even if it did I
would have empty rows inbetween the rows I want.

I also looked at Pivot Tables, but couldn't really put my mind around
that...

So, is it possible to do this with worksheet functions? Or do I have to go
the VBA way, and generate my lists 'manually'?

I'm usually pretty good finding stuff with Google, but I have a hard time
finding appropriate search words for this...

Thank you!

Bruno G.

Wow! Fantastic!!

Good thing I asked... I would still be trying to find that well into next
year...
I thought it would be simpler than that.

Well, now I'm off to study all those formulas - they are impressive, but I'm
sure I'll be able to manage. ;-)

Thank you so much Domenic!!

Bruno G.

"Domenic" wrote in message
...
Try the following formula-based approach...

Assuming that Sheet1 contains your source table, enter the following on
Sheet2...

A1: enter 'Year', which is just a label

B1: enter 'Date Paid', which is just a label

A2: enter your first criteria (Year), for example 2004

B2: enter your second criteria (Date Paid), for example 2005

C1: enter a 0 (zero)

C2: copied down:

=IF((Sheet1!A2<"")*(YEAR(Sheet1!A2)=$B$2)*(Sheet1 !B2=$A$2),LOOKUP(9.9999
9999999999E+307,$C$1:C1)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

E2, copied down:

=IF(ROW()-ROW($E$2)+1<=$D$1,MATCH(ROW()-ROW($E$2)+1,C:C,0),"")

F2, copied down and across:

=IF(N($E2),INDEX(Sheet1!A:A,$E2),"")

Now, change your criteria for A2 and B2 to return a list of the
corresponding information.

Hope this helps!

In article ,
"Bruno G." wrote:

Here's what I would like to do, starting with the following data:

Date Year Amount Description
25/04/04 2005 25.65 Aaaa
26/04/04 2004 32.25 Bbbb
27/04/04 2004 48.54 Cccc
28/04/04 2005 63.24 Dddd
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
03/01/05 2005 48.57 Gggg
04/01/05 2004 73.50 Hhhh

I have a table with 4 columns: Date the payment is made, Year for wich
the
payment is made, Amount and Description. Each columns is associated with
a
dynamic named range (AllDates, AllYears, AllAmounts, AllDesc).

On a different worksheet, I would like to automatically list the rows in
which a payment is made for a different year, with separate lists for
payments for 2004 made in 2005 and for payments for 2005 made in 2004.
Something like that:

For 2004 (payments made in 2005):
Date Year Amount Description
01/01/05 2004 14.25 Eeee
02/01/05 2004 96.57 Ffff
04/01/05 2004 73.50 Hhhh

For 2005 (payments made in 2004):
Date Year Amount Description
25/04/04 2005 25.65 Aaaa
28/04/04 2005 63.24 Dddd

I'm guessing that I need some kind of array formula, but can't really
find a
way to do it...
The only thing I could come up with is
{ =IF(AND(YEAR(AllDates)=2004;AllYears=2005);AllDate s;"") }
but it doesn't return anything (no errors either), and even if it did I
would have empty rows inbetween the rows I want.

I also looked at Pivot Tables, but couldn't really put my mind around
that...

So, is it possible to do this with worksheet functions? Or do I have to
go
the VBA way, and generate my lists 'manually'?

I'm usually pretty good finding stuff with Google, but I have a hard time
finding appropriate search words for this...

Thank you!

Bruno G.