Get names on other sheet with 2-3 conditions

vra

Board Regular
Joined
Apr 16, 2011
Messages
95
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Excel Workbook
ABCD
2JOURNAL ENTRIES
3DRCR
4INVESTMENTS5000
5CASH5000
6INVESTMENTS12000
7BANK OF MAH12000
8BANK OF MAH2000
9INVESTMENTS2000
10INVESTMENTS7000
11CASH7000
12INVESTMENTS11000
13cash10000
14INTEREST RECEIVED1000
15
journal
Excel 2010
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
BCDE
4INVESTMENTS
5
6DEBITCREDITBALANCE
7OPENING BAL10000001000000
8cash500001005000
9Bank of mah1200001017000
10Bank of mah020001015000
11Cash700001022000
12cash1100001033000
131033000
141033000
151033000
161033000
171033000
181033000
191033000
201033000
211033000
221033000
ASSETS
Excel 2010
Cell Formulas
RangeFormula
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

vra

Board Regular
Joined
Apr 16, 2011
Messages
95
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Pls see above & guide how I can update Assets sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top