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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Pls see above & guide how I can update Assets sheet
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top