Snasui Excel Forum

Hi Dear,

I need your help,I google and come up with formula as in Schedule Acc AIP Print.
I need to drill down amount from column I and J .it would give me three line down.for example if I6 has value but J6 blank it will give me amount in P5 and PL Acc in Q5 in P6 and Q6 blank since no amount tax and P7 it return result from clearing amount minus amount tax it means offset between P5 and P6 would give P7 if P5 is 20335.62 P6 -300 so P7 20035.62.

It would give every 3 rows as in sample file


Thanks

P5 =CHOOSE(MOD(ROWS($W$5:$W5)+2,3)+1,IF(OFFSET($P$6,(@CELL("row",P5)-5)/2,-7)<>0,OFFSET($P$6,(@CELL("row",P5)-5)/2,-7),""),IF(OFFSET($P$6,(@CELL("row",P5)-5)/2,-6)<>"",-OFFSET($P$6,(@CELL("row",P5)-5)/2,-6),""),IF(OFFSET($P$6,(@CELL("row",P5)-5)/2,-7)<>"",IF(OFFSET($P$6,(@CELL("row",P5)-5)/2,-7)<>"",-OFFSET($P$6,(@CELL("row",P5)-5)/2,-7)-OFFSET($P$6,(@CELL("row",P5)-5)/2,-6),-OFFSET($P$6,(@CELL("row",P5)-5)/2,-7)),""))

Q5 =CHOOSE(MOD(ROWS($X$5:$X5)+2,3)+1,IF(OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-6)<>0,OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-6),""),IF(OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-7)<>"",OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-10)&OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-4),""),IF(OFFSET($P$6,(@CELL("row",P6)-6)/2,-8)<>"",OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-10)&OFFSET($Q$6,(@CELL("row",Q6)-6)/2,-5),""))

U5 =CHOOSE(MOD(ROWS($X$5:$X5)+2,3)+1,IF(P5<>"","[ "&IF(OFFSET($U$6,(@CELL("row",U6)-6)/2,-6)<>0,OFFSET($U$6,(@CELL("row",U6)-6)/2,-6),"")&" ] "&TEXT(TODAY(),"MMM.yyyy"),""),"","")

Hi thank will take a look.BTW without Offset how we use INDEX?

I check it is not correct .three lines it should be

P5:20335.62 PL64023
P6:-300 USD172260001
P7:-20035.62 USD170230001

try.

P5 =@CHOOSE(MOD(ROWS(P$5:P5)+2,3)+1,INDEX($A$6:$O$15,((ROWS(P$5:P5)-1)/3)+1,MATCH("Clearing Amount",$A$5:$O$5,0)),-@INDEX($A$6:$O$15,((ROWS(P$5:P5)-1)/3)+1,MATCH("Amount Tax",$A$5:$O$5,0)),-@INDEX($A$6:$O$15,((ROWS(P$5:P5)-1)/3)+1,MATCH("Clearing Amount",$A$5:$O$5,0))-@INDEX($A$6:$O$15,((ROWS(P$5:P5)-1)/3)+1,MATCH("Amount Tax",$A$5:$O$5,0)))

Q5 =@CHOOSE(MOD(ROWS(Q$5:Q5)+2,3)+1,INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("PL Account",$A$5:$O$5,0)),IF(-@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("Tax Account",$A$5:$O$5,0))<>0,@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("CCY",$A$5:$O$5,0))&@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("Tax Account",$A$5:$O$5,0)),""),IF(-@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("Loan- Account ",$A$5:$O$5,0))<>0,@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("CCY",$A$5:$O$5,0))&@INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("Loan- Account ",$A$5:$O$5,0)),""))

R5 =IF(P5<>0,"0001-BNK","")

S5 =IF(P5<>0,"1000-HQ","")

T5 =IF(P5<>0,INDEX($A$6:$O$15,((ROWS(Q$5:Q5)-1)/3)+1,MATCH("CCY",$A$5:$O$5,0)),"")

U5 =CHOOSE(MOD(ROWS(U$5:U5)+2,3)+1,"[ "&@INDEX($A$6:$O$15,((ROWS(U$5:U5)-1)/3)+1,MATCH("T24 Narrative (16 Degits)",$A$5:$O$5,0))&" ] "&TEXT(TODAY(),"MMM.yyyy"),"","")

Thank you so much