Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
2 | JOURNAL ENTRIES | |||||
3 | DR | CR | ||||
4 | INVESTMENTS | 5000 | ||||
5 | CASH | 5000 | ||||
6 | INVESTMENTS | 12000 | ||||
7 | BANK OF MAH | 12000 | ||||
8 | BANK OF MAH | 2000 | ||||
9 | INVESTMENTS | 2000 | ||||
10 | INVESTMENTS | 7000 | ||||
11 | CASH | 7000 | ||||
12 | INVESTMENTS | 11000 | ||||
13 | cash | 10000 | ||||
14 | INTEREST RECEIVED | 1000 | ||||
15 | ||||||
journal |
This data is on Journal Sheet.
Then i have two more sheets named Assets & Liabilities where Accounts are made from journal sheet.
I want in column B below Op balance
name of Accounts by which investment A/c is debited or credited as in Journal sheet. Answer should come in Liabilities & Assets sheet for the accounts i select. Answer should come in such way:
Excel Workbook | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
4 | INVESTMENTS | |||||
5 | ||||||
6 | DEBIT | CREDIT | BALANCE | |||
7 | OPENING BAL | 1000000 | 1000000 | |||
8 | cash | 5000 | 0 | 1005000 | ||
9 | Bank of mah | 12000 | 0 | 1017000 | ||
10 | Bank of mah | 0 | 2000 | 1015000 | ||
11 | Cash | 7000 | 0 | 1022000 | ||
12 | cash | 11000 | 0 | 1033000 | ||
13 | 1033000 | |||||
14 | 1033000 | |||||
15 | 1033000 | |||||
16 | 1033000 | |||||
17 | 1033000 | |||||
18 | 1033000 | |||||
19 | 1033000 | |||||
20 | 1033000 | |||||
21 | 1033000 | |||||
22 | 1033000 | |||||
ASSETS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B13 | =IF(ISERROR($A13)=TRUE,"",INDIRECT("journal!A"&A13+1)) | |
B14 | =IF(ISERROR($A14)=TRUE,"",INDIRECT("journal!A"&A14+1)) | |
D8 | =IF(ISERROR($A8)=TRUE,"",INDIRECT("journal!D"&$A8)) | |
D9 | =IF(ISERROR($A9)=TRUE,"",INDIRECT("journal!D"&$A9)) | |
D10 | =IF(ISERROR($A10)=TRUE,"",INDIRECT("journal!D"&$A10)) | |
D11 | =IF(ISERROR($A11)=TRUE,"",INDIRECT("journal!D"&$A11)) | |
D12 | =IF(ISERROR($A12)=TRUE,"",INDIRECT("journal!D"&$A12)) | |
D13 | =IF(ISERROR($A13)=TRUE,"",INDIRECT("journal!D"&$A13)) | |
D14 | =IF(ISERROR($A14)=TRUE,"",INDIRECT("journal!D"&$A14)) | |
D15 | =IF(ISERROR($A15)=TRUE,"",INDIRECT("journal!D"&$A15)) | |
D16 | =IF(ISERROR($A16)=TRUE,"",INDIRECT("journal!D"&$A16)) | |
D17 | =IF(ISERROR($A17)=TRUE,"",INDIRECT("journal!D"&$A17)) | |
D18 | =IF(ISERROR($A18)=TRUE,"",INDIRECT("journal!D"&$A18)) | |
C7 | =INDEX(GROUPS!$C$3:$C$16,MATCH(ASSETS!$B$4,GROUPS!$A$3:$A$12,0)) | |
C8 | =IF(ISERROR($A8)=TRUE,"",INDIRECT("journal!C"&$A8)) | |
C9 | =IF(ISERROR($A9)=TRUE,"",INDIRECT("journal!C"&$A9)) | |
C10 | =IF(ISERROR($A10)=TRUE,"",INDIRECT("journal!C"&$A10)) | |
C11 | =IF(ISERROR($A11)=TRUE,"",INDIRECT("journal!C"&$A11)) | |
C12 | =IF(ISERROR($A12)=TRUE,"",INDIRECT("journal!C"&$A12)) | |
C13 | =IF(ISERROR($A13)=TRUE,"",INDIRECT("journal!C"&$A13)) | |
C14 | =IF(ISERROR($A14)=TRUE,"",INDIRECT("journal!C"&$A14)) | |
C15 | =IF(ISERROR($A15)=TRUE,"",INDIRECT("journal!C"&$A15)) | |
C16 | =IF(ISERROR($A16)=TRUE,"",INDIRECT("journal!C"&$A16)) | |
C17 | =IF(ISERROR($A17)=TRUE,"",INDIRECT("journal!C"&$A17)) | |
C18 | =IF(ISERROR($A18)=TRUE,"",INDIRECT("journal!C"&$A18)) | |
C19 | =IF(ISERROR($A19)=TRUE,"",INDIRECT("journal!C"&$A19)) | |
C20 | =IF(ISERROR($A20)=TRUE,"",INDIRECT("journal!C"&$A20)) | |
E7 | =C7-D7 | |
E8 | =IFERROR(E7+C8-D8,E7) | |
E9 | =IFERROR(E8+C9-D9,E8) | |
E10 | =IFERROR(E9+C10-D10,E9) | |
E11 | =IFERROR(E10+C11-D11,E10) | |
E12 | =IFERROR(E11+C12-D12,E11) | |
E13 | =IFERROR(E12+C13-D13,E12) | |
E14 | =IFERROR(E13+C14-D14,E13) | |
E15 | =IFERROR(E14+C15-D15,E14) | |
E16 | =IFERROR(E15+C16-D16,E15) | |
E17 | =IFERROR(E16+C17-D17,E16) | |
E18 | =IFERROR(E17+C18-D18,E17) | |
E19 | =IFERROR(E18+C19-D19,E18) | |
E20 | =IFERROR(E19+C20-D20,E19) | |
E21 | =IFERROR(E20+C21-D21,E20) | |
E22 | =IFERROR(E21+C22-D22,E21) |
Thanks In advance!!
Cell b13 & B14 formula will not be useful for all B column cells ..
Any suggestion