Snasui Excel Forum

Hi Dear!

I have a template for listing accrued expenses .
I want to make a journal entry from Menu Sheet and Transfer to Journal Sheet .I need only formula.the expected Result in Journal Sheet also provided.

BTW how to arrange data in Menu Sheet to be easier to transfer to sheet Journal.

Best wishes,

Try with these formulas:

Sheet Menu

1. K8
   =IF(G8="","",COUNTIF(OFFSET(H8,0,0,IFERROR(MATCH(TRUE,INDEX(G9:$G$300<>"",0),0),1)),"<>")+1)
   Enter > Copy down
2. L8
   =IF(K8="","",SUM(K$8:K8)-K8+1)
   Enter > Copy down

Sheet Journal

1. A11
   =IF(ROWS(A$11:A11)=LOOKUP(ROWS(A$11:A11),Menu!$L$8:$L$32),LOOKUP(ROWS(A$11:A11),Menu!$L$8:$L$32,Menu!$G$8:$G$32),"")
   Enter > Copy down
2. B11
   =IF(A11<>"","",""&INDEX(Menu!$H$8:$H$300,MATCH(ROWS(B$11:B11),Menu!$L$8:$L$32)+ROWS(B$11:B11)-MATCH(2,INDEX(1/($A$11:A11<>""),0))-1))
   Enter > Copy down

Thank you one more thing how we can get amount from total price?

Please attach the latest file with formula above and explain the result what you want.

Hi Dear,

I put some examples of amount in journal .it should come from menu in column I and place in journal column D.

Thanks

Your data on sheet Menu should look like this picture.
Sheet Journal cell D11 can input this formula.

=IF(ROWS(A$11:A11)=LOOKUP(ROWS(A$11:A11),Menu!$M$8:$M$32),LOOKUP(ROWS(A$11:A11),Menu!$M$8:$M$32,Menu!$H$8:$H$32),-INDEX(Menu!$J$8:$J$300,MATCH(ROWS(B$11:B11),Menu!$M$8:$M$32)+ROWS(B$11:B11)-MATCH(2,INDEX(1/($A$11:A11<>""),0))-1))

Enter and copy down.

Thank you so much