Populate Balance in last row based on sheet name

[Mussala]

Hello
I want populating balance in cell I4 based on cell G4 in VOUCH sheet . the cell G4 should match based on sheet name .
if the sheet name is wrong in G4 then doesn't populate balance . when populate balance should be based on last row for column E

BL.xlsm
	A	B	C	D	E
1	DATE	ACCOUNT	DEBIT	CREDIT	BALANCE
2	01/01/2021	SALES S004	2,000.00		2,000.00
3	02/01/2021	PUR A003	1,000.00	2,000.00	1,000.00
4	03/01/2021	PUR A004	1,200.00	3,000.00	-800.00
5	04/01/2021	SALES S004	2,000.00		1,200.00
6	05/01/2021	SALES S005	2,300.00		3,500.00
7	06/01/2021	SALES S006	6,000.00	100.00	9,400.00
8	07/01/2021	SALES S007	1,400.00		10,800.00
MUSSA

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E8	E3	=E2+C3-D3

BL.xlsm
	G	H	I
3	SHEET NAME		AMOUNT
4
vouch

expected

BL.xlsm
	G	H	I
3	SHEET NAME		AMOUNT
4	MUSSA		10,800.00
vouch

 

[Joe4]

Try this formula in cell I4 of your "vouch" sheet:

=INDEX(INDIRECT(G4&"!E:E"),COUNTA(INDIRECT(G4&"!E:E")))

or this one:

=LOOKUP(2,1/(INDIRECT(G4&"!E:E")<>""),INDIRECT(G4&"!E:E"))

 

[Mussala]

I 'm surprised from the formula !
I expected just one way by vba , but the formula is very great .
many thanks for your help .

 

[Joe4]

You are welcome!
Glad we were able to help!