Display specific rows from table on other worksheet

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);AllDates;"") }
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 <[email protected]>,
"Bruno G." <[email protected]> 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);AllDates;"") }
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Bruno G." <[email protected]> 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);AllDates;"") }
>> 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.