Excel OB and CB

[alliswell]

Hi, everybody. in column A are dates, in column B is OB(opening balance) and CB(closing balance for the current date above. Now when i enter date in A12, for example 03/03/2025 and in B12 i enter OB then excel should find previous date CB in C12 that is 690, (previous date CB was 690) 02/03/2025

 

[Peter_SSs]

Is this formula in C12 what you want?

=IF(B12="OB",LOOKUP(9^9,C$1:C11),"")

 

[alliswell]

Is this formula in C12 what you want?

=IF(B12="OB",LOOKUP(9^9,C$1:C11),"")

Mr peter its not working

Now when i put date in A16 that is 04/03/2025 and "OB" in B16 then excel should find CB of previous date and put its value in C16. Previous last date was 03/03/2025 and its CB was 1090, so now in C16 i must get 1090. I just want to carry forward last date closing balance(CB) to new date as opening balance(OB) and so on... to every new date i enter.

Sorry as i was unable to explain properly

Thanks and regards

 

[Peter_SSs]

1. Does that mean that you are looking for a vba solution?
   
   
2. How did the 1090 get into C14?

 

[alliswell]

1. Does that mean that you are looking for a vba solution?
   
   
2. How did the 1090 get into C14?

Mr peter i just want the closing balance of 03/03/2025 in C16 of 04/03/2025

now just look, i have 2 transactions for date 03/03/2025 in column A. And last transaction of 03/03/2025 is in row A13. Now i want some tricky formula so that when i enter the date 04/03/2025 in A16 and OB in B16 then excel must check the last non-blank row of previous date 03/03/2025 and get the the previous
date CB from C10 in C16

I dont need VBA, its better if you provide nice formula. I know Mr peter have the solution. You solved all of my complex queries. You are the best. Everyone on MrExcel is genius but your tuning with me is that you understand what i want.

Thanks a lot.

 

[MARK858]

I can't see an way of doing your request in column C without writing a separate formula for each set of dates.
If you were using a separate column you could do the below.

Book1
	A	B	C	D
1	01/03/2025	OB	500
2	01/03/2025		100
3	01/03/2025		-30
4	01/03/2025		200
5		CB		500
6
7	02/03/2025	OB	770
8	02/03/2025		-50
9	02/03/2025		-30
10		CB		770
11
12	03/03/2025	OB	690
13	03/03/2025		400
14		CB		690
Sheet1

Cell Formulas
Range	Formula
D1:D14	D1	=IF(B1="CB",LOOKUP(2,1/($B$1:$B1="OB"),$C$1:$C1),"")

 

[MARK858]

Actually ignore my last post as you are mentioning the previous date. Please post using XL2BB a manually entered example with the results that you want highlighted.

 

[alliswell]

Actually ignore my last post as you are mentioning the previous date. Please post using XL2BB a manually entered example with the results that you want highlighted.

See the image attached Mr. Mark
Can index match do the job ?
Or suggest any nice tricky formula
Thanks and regard

 

[Sam_D_Ben]

May be you can try this,

Book1
	A	B	C
1	Date	Type	Amount
2	1/3/2025	OB	500
3	1/3/2025		100
4	1/3/2025		-30
5	1/3/2025		200
6		CB	770
7	2/3/2025	OB	770
8	2/3/2025	OB	770
9	2/3/2025		-50
10	2/3/2025		-30
11		CB	690
12	3/3/2025	OB	690
13	3/3/2025	OB	690
14	3/3/2025		400
15		CB	1090
16	4/3/2025	OB	1090
Sheet2

Cell Formulas
Range	Formula
C7,C16,C12	C7	=IF(B7="OB", LOOKUP(2,1/(($A$1:$A$16<A7)*($B$1:$B$16="CB")*(ROW($A$1:$A$16)<ROW())), $C$1:$C$16), "")

Book1
	A	B	C
1	Date	Type	Amount
2	1/3/2025	OB	500
3	1/3/2025		100
4	1/3/2025		-30
5	1/3/2025		200
6		CB	770
7	2/3/2025	OB	770
8	2/3/2025	OB	770
9	2/3/2025		-50
10	2/3/2025		-30
11		CB	690
12	3/3/2025	OB	690
13	3/3/2025	OB	690
14	3/3/2025		400
15		CB	1090
16	4/3/2025	OB	1090
Sheet1

Cell Formulas
Range	Formula
C7,C16,C12	C7	=IF($B$2="OB", LOOKUP(2,1/((A$1:INDIRECT("A"&ROW()-1)<$A$2)*(B$1:INDIRECT("B"&ROW()-1)="CB")), C$1:INDIRECT("C"&ROW()-1)), "")

 

[Peter_SSs]

How did the 1090 get into C14?

No answer to this question?

Or suggest any nice tricky formula

It does not need to be tricky. Just copy the formula that I gave you for C12 and paste it into any cell in column C that has "OB" in column B of that row.