Thanks Max,
this was already very helpful, especially since I have never worked with
arrays before. Just one more question: How do I get the array formula to add
together several entries that are, say [daily] and of the same date?
At the moment the formula seems to only take the first value it finds, is
that right?
Thanks for any help!
Nico
"Max" wrote:
Assume:
Table 1 is in Sheet1, A1:C5
Table 2 is in Sheet2, A1:B3
Table 3 is in Sheet3, A1:D5
In Sheet1
----------
Put in D2:
=IF(ISNUMBER(MATCH($C2,Sheet2!A:A,0)),Sheet2!A$1,I F(ISNUMBER(MATCH($C2,Sheet
2!B:B,0)),Sheet2!B$1,"[Daily]"))
(normal ENTER will do)
Copy down to D5
In Sheet3
----------
Put in formula bar for B2, and array-enter
(i.e. press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$B$2:$B$10&"_"&S heet1!$D$2:$D$10,0)),0,IND
EX(Sheet1!$A$2:$A$10,MATCH($A2&"_"&B$1,Sheet1!$B$2 :$B$10&"_"&Sheet1!$D$2:$D$
10,0)))
Copy across to D2, fill down to D5 to populate the grid
Sheet3 returns the desired results,
i.e. for the sample data posted:
[Date] [Monthly] [Weekly] [Daily]
30-Apr 0 0 0
1-May 1200 300 12
2-May 0 0 0
3-May 0 0 50
(Note: Typo in date corrected: "30-Apr")
Adapt the ranges in the array formula, viz.:
Sheet1!$B$2:$B$10
Sheet1!$D$2:$D$10
Sheet1!$A$2:$A$10
to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"nico" wrote in message
...
I have a table with three columns: Amount, Date, Description
In a second table certain descriptions are grouped under headers: Monthly,
Weekly
I would now like to set up a third table that has the date as a running
number, and
then the columns Monthly, Weekly, Daily. Under these headings I would like
the amount from the first table to appear in the Monthly column if the
date
from table one is the same as the date in the row of table 3, and if the
description in table one is entered under the Monthly column of table 2.
If
the description is not in table2 either under weekly or monthly, I want
the
amount to enter Table 3 in the Daily column, at the right date. Is that
possible?
Table1
[Amount] [Date] [Description]
1200 1-May Rent
300 1-May Transport
12 1-May Stuff
50 3-May More Stuff
Table2
[Monthly] [Weekly]
Rent Transport
Taxes Cleaners
Table3
[Date] [Monthly] [Weekly] [Daily]
31-Apr 0 0 0
1-May 1200 300 12
2-May 0 0 0
3-May 0 0 50
The dates in Table3 are pre-entered.
Any help greatly appreciated!
|