Home |
Search |
Today's Posts |
#1
|
|||
|
|||
copy data from one sheet to another under conditions
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
Just change the formula in Sheet3 ..
Put instead in B2: =SUMPRODUCT((Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$ D$10=B$1),Sheet1!$A$2:$A$1 0) (Normal ENTER will do) Copy across to D2, fill down to D5 to populate the grid .... and think this is the formula that should have been suggested in the first place <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "nico" wrote in message ... 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 |
#5
|
|||
|
|||
Thanks Max, that works! Brilliant!
Nico "Max" wrote: Just change the formula in Sheet3 .. Put instead in B2: =SUMPRODUCT((Sheet1!$B$2:$B$10=$A2)*(Sheet1!$D$2:$ D$10=B$1),Sheet1!$A$2:$A$1 0) (Normal ENTER will do) Copy across to D2, fill down to D5 to populate the grid .... and think this is the formula that should have been suggested in the first place <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "nico" wrote in message ... 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 |
#6
|
|||
|
|||
You're welcome !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "nico" wrote in message ... Thanks Max, that works! Brilliant! Nico |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Multiple worksheet queries | Excel Worksheet Functions | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
getting data from 2 different exc sheet | Excel Worksheet Functions |