• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

account statement

J

jack999

Member
I would like to make an account statement as per attached excel file.There is macro but not achieve the actual result.If somebody give a solution highly appreciated.


Accounts Statement.xlsm

http://www38.zippyshare.com/v/20664243/file.html


I have foxpro code if you need I can post here for reference.
 
Hi Jack999


Please see this sheet, every thing is formula based i haven't touched the code:


http://dl.dropbox.com/u/60644346/Accounts%20Statement-Chandoo's%20Solution.xlsm


Regards,
 
Hi try this:


http://dl.dropbox.com/u/60644346/Accounts%20Statement-Chandoos%20Solution.xlsm


Opps, and apostrophe in my last file's name was problematic...


Regards,
 
@jack


Hi


Please Try This also which is totally used with formula


https://dl.dropbox.com/u/75654703/Accounts%20Statement.xlsm


Thanks


SP
 
http://www19.zippyshare.com/v/42444925/file.html


@sgmpatnaik

Here showing one error when I try to change the name. Why this error showing, how I can correct the formula,
 
@jack


Hi


You are getting the error for the below reason that is


you are using the formula in B6 as


=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B6),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$3=$B$2,ROW('Invoice-Cash-Cheque'!$B$3:$B$3)),ROW(A1))))


and B7 as


=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B7),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$4=$B$2,ROW('Invoice-Cash-Cheque'!$B$3:$B$4)),ROW(A2))))


in the B7 which is reference to B6 so B6 Formula and B7 formula is difference in the below part


=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B6),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$3=$B$2


difference


=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B7),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$4=$B$2,


mistake is here


$B$3:$B$3 and $B$3:$B$4


which formula you are using the same formula to be use in the B7


Hope you clear other wise please inform


Thanks


SP
 
B6=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B6),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$4=$B$2,ROW('Invoice-Cash-Cheque'!$B$3:$B$4)),ROW(A1))))


B7=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($B$6:B7),"",INDEX('Invoice-Cash-Cheque'!C:C,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$4=$B$2,ROW('Invoice-Cash-Cheque'!$B$3:$B$4)),ROW(A2))))


Then also showing error #Value!,


Can you reply by attach file in by Highlight with colour.


Thanks&Regards
 
@jack


Hi


The above mention formulas are array formula so you have to enter as Ctrl+Shift+Enter not simple enter


and i will try to upload the file using with Macro


if still you will face problem then please inform


Thanks


SP
 
yes I can't find a solution. I try to enter Ctrl+Shift+Enter but I can't. I don't know why? If you trying to fix with macro pls. disply all sheet one by one one sheet without any format ie border line or anything


Thanks & Regar4ds
 
I suggested you a solution in my second-last post, does that work for you or you are looking for something else?
 
I posted a solution here:


http://dl.dropbox.com/u/60644346/Accounts%20Statement-Chandoos%20Solution.xlsm


Can you be more specific what formula is not working,i can see SP has posted many formula?? Please be specific.


Faseeh
 
taking about J4 showing NA-Error? That is bcz your workbook doesn't have sheet named Opening Balance!!
 
=IF(COUNTIF('Invoice-Cash-Cheque'!B:B,$B$2)<ROWS($G$6:G7),"",INDEX('Invoice-Cash-Cheque'!F:F,SMALL(IF('Invoice-Cash-Cheque'!$B$3:$B$4=$B$2,ROW('Invoice-Cash-Cheque'!$B$3:$B$4)),ROW(C2))))


How can I copy this array formula from top cell to last cell
 
You must log in or register to reply here.
Back
Top