Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help!!!
Sheet 1:
Date Item 20/09/2006 abc 20/09/2006 abc 22/09/2006 xyz 22/09/2006 abc 26/09/2006 efg Sheet 2: item dept abc Dept A xyz Dept B efg Dept A Sheet 3: Date Item Payout 20/09/2006 abc 100 22/09/2006 abc 110 23/09/2006 xyz 120 25/09/2006 abc 140 26/09/2006 efg 140 This is date wise item's payout. Based on this data I need below mentioned detail Date Dept A (total payout) Dept B(total Payout) 20/09/2006 21/09/2006 22/09/2006 23/09/2006 24/09/2006 25/09/2006 26/09/2006 1) Look into sheet 1 and count how many time dept A or B is there 2) Multiply that no. with payout given in sheet 3. Here comes the realy problem. Lets say if on 20/09/06 payout is 100 rs. for abc and payout changes on 25/09/06 then from 20th to 24th payout will be 100 rs and it will change on 25/09/06. Suppose it is clear to you all. Please help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help!!!
This is not very clear.
Dept A or B is not on Sheet1. Why are you multiplying the payout instead of adding it up? The logic that you want to accomplish is not clear. On Sheet2, can more than one department be matched with more than one item? On Sheet3, can an item appear more than once for each date? Anyway, here's what I came up with. Array-entered into cell B2 (first below Dept A). Delete the "(total payout)" part; you can add a row for it above, later if you really need it. Copy down and over. Adjust references if items are added to the list, obviously, or use named ranges. I'm assuming this result table is on Sheet4. =SUM((Sheet1!$A$2:$A$6=Sheet4!$A2)*(Sheet1!$B$2:$B $6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0))))*SU M((Sheet3!$C$2:$C $6)*(Sheet3!$A$2:$A$6=Sheet4!$A2)*(Sheet3!$B$2:$B$ 6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0)))) I think this does what you want. On Sep 10, 3:04 am, Excel_Learner wrote: Sheet 1: Date Item 20/09/2006 abc 20/09/2006 abc 22/09/2006 xyz 22/09/2006 abc 26/09/2006 efg Sheet 2: item dept abc Dept A xyz Dept B efg Dept A Sheet 3: Date Item Payout 20/09/2006 abc 100 22/09/2006 abc 110 23/09/2006 xyz 120 25/09/2006 abc 140 26/09/2006 efg 140 This is date wise item's payout. Based on this data I need below mentioned detail Date Dept A (total payout) Dept B(total Payout) 20/09/2006 21/09/2006 22/09/2006 23/09/2006 24/09/2006 25/09/2006 26/09/2006 1) Look into sheet 1 and count how many time dept A or B is there 2) Multiply that no. with payout given in sheet 3. Here comes the realy problem. Lets say if on 20/09/06 payout is 100 rs. for abc and payout changes on 25/09/06 then from 20th to 24th payout will be 100 rs and it will change on 25/09/06. Suppose it is clear to you all. Please help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help!!!
No sorry, that won't work. The Index to find department only finds
the first instance, and therefore will not work for subsequent items. Let me see if I can find another way. On Sep 10, 11:25 am, iliace wrote: This is not very clear. Dept A or B is not on Sheet1. Why are you multiplying the payout instead of adding it up? The logic that you want to accomplish is not clear. On Sheet2, can more than one department be matched with more than one item? On Sheet3, can an item appear more than once for each date? Anyway, here's what I came up with. Array-entered into cell B2 (first below Dept A). Delete the "(total payout)" part; you can add a row for it above, later if you really need it. Copy down and over. Adjust references if items are added to the list, obviously, or use named ranges. I'm assuming this result table is on Sheet4. =SUM((Sheet1!$A$2:$A$6=Sheet4!$A2)*(Sheet1!$B$2:$B $6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0))))*SU M((Sheet3!$C$2:$C $6)*(Sheet3!$A$2:$A$6=Sheet4!$A2)*(Sheet3!$B$2:$B$ 6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0)))) I think this does what you want. On Sep 10, 3:04 am, Excel_Learner wrote: Sheet 1: Date Item 20/09/2006 abc 20/09/2006 abc 22/09/2006 xyz 22/09/2006 abc 26/09/2006 efg Sheet 2: item dept abc Dept A xyz Dept B efg Dept A Sheet 3: Date Item Payout 20/09/2006 abc 100 22/09/2006 abc 110 23/09/2006 xyz 120 25/09/2006 abc 140 26/09/2006 efg 140 This is date wise item's payout. Based on this data I need below mentioned detail Date Dept A (total payout) Dept B(total Payout) 20/09/2006 21/09/2006 22/09/2006 23/09/2006 24/09/2006 25/09/2006 26/09/2006 1) Look into sheet 1 and count how many time dept A or B is there 2) Multiply that no. with payout given in sheet 3. Here comes the realy problem. Lets say if on 20/09/06 payout is 100 rs. for abc and payout changes on 25/09/06 then from 20th to 24th payout will be 100 rs and it will change on 25/09/06. Suppose it is clear to you all. Please help.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help!!!
I would recommend the following design change to your worksheet:
Sheet1: Date Item Dept 20/09/2006 abc =VLOOKUP(B2,Sheet2!$A$2:$B$4,2,FALSE) 20/09/2006 abc =VLOOKUP(B3,Sheet2!$A$2:$B$4,2,FALSE) 22/09/2006 xyz =VLOOKUP(B4,Sheet2!$A$2:$B$4,2,FALSE) 22/09/2006 abc =VLOOKUP(B5,Sheet2!$A$2:$B$4,2,FALSE) 26/09/2006 efg =VLOOKUP(B6,Sheet2!$A$2:$B$4,2,FALSE) Sheet3: Date Item Payout Dept 20/09/2006 abc 100 =VLOOKUP(B2,Sheet2!$A$2:$B$4,2,FALSE) 22/09/2006 abc 110 =VLOOKUP(B3,Sheet2!$A$2:$B$4,2,FALSE) 23/09/2006 xyz 120 =VLOOKUP(B4,Sheet2!$A$2:$B$4,2,FALSE) 25/09/2006 abc 140 =VLOOKUP(B5,Sheet2!$A$2:$B$4,2,FALSE) 26/09/2006 efg 140 =VLOOKUP(B6,Sheet2!$A$2:$B$4,2,FALSE) Then, the following formula on Sheet4, in B2 and copy down/over: =SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet4!$A2),--(Sheet1!$C$2:$C$6=Sheet4! B$1))*SUMPRODUCT(--(Sheet3!$D$2:$D$6=Sheet4!B$1),--(Sheet3!$A$2:$A $6=Sheet4!$A2),Sheet3!$C$2:$C$6) On Sep 10, 12:21 pm, iliace wrote: No sorry, that won't work. The Index to find department only finds the first instance, and therefore will not work for subsequent items. Let me see if I can find another way. On Sep 10, 11:25 am, iliace wrote: This is not very clear. Dept A or B is not on Sheet1. Why are you multiplying the payout instead of adding it up? The logic that you want to accomplish is not clear. On Sheet2, can more than one department be matched with more than one item? On Sheet3, can an item appear more than once for each date? Anyway, here's what I came up with. Array-entered into cell B2 (first below Dept A). Delete the "(total payout)" part; you can add a row for it above, later if you really need it. Copy down and over. Adjust references if items are added to the list, obviously, or use named ranges. I'm assuming this result table is on Sheet4. =SUM((Sheet1!$A$2:$A$6=Sheet4!$A2)*(Sheet1!$B$2:$B $6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0))))*SU M((Sheet3!$C$2:$C $6)*(Sheet3!$A$2:$A$6=Sheet4!$A2)*(Sheet3!$B$2:$B$ 6=INDEX(Sheet2!$A $2:$A$4,MATCH(Sheet4!B$1,Sheet2!$B$2:$B$4,0)))) I think this does what you want. On Sep 10, 3:04 am, Excel_Learner wrote: Sheet 1: Date Item 20/09/2006 abc 20/09/2006 abc 22/09/2006 xyz 22/09/2006 abc 26/09/2006 efg Sheet 2: item dept abc Dept A xyz Dept B efg Dept A Sheet 3: Date Item Payout 20/09/2006 abc 100 22/09/2006 abc 110 23/09/2006 xyz 120 25/09/2006 abc 140 26/09/2006 efg 140 This is date wise item's payout. Based on this data I need below mentioned detail Date Dept A (total payout) Dept B(total Payout) 20/09/2006 21/09/2006 22/09/2006 23/09/2006 24/09/2006 25/09/2006 26/09/2006 1) Look into sheet 1 and count how many time dept A or B is there 2) Multiply that no. with payout given in sheet 3. Here comes the realy problem. Lets say if on 20/09/06 payout is 100 rs. for abc and payout changes on 25/09/06 then from 20th to 24th payout will be 100 rs and it will change on 25/09/06. Suppose it is clear to you all. Please help.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|