Repeat or Keep previous value while On/Off condition met

[GeeWhiz7]

Hi Gurus,
I have a table of values that I would like repeat in a new column only if certain condition is met.
In this case, Column 1 has the values, Column 2 has the condition On=1, Off=Blank.

Id like column 3 to have values equal to column 1 if column 2 is blank and while column 2 is on=value of 1, I would like to repeat the first value (12 in this case) until the condition is not met.
I can figure this out with multiple columns, but can't quite get it to work in this simple form.

Ideas are appreciated! Thanks!

 

[Jeffrey Mahoney]

Cell C3: =IF(AND(B3=1,B2=1),C2,A3)

 

[GeeWhiz7]

Cell C3: =IF(AND(B3=1,B2=1),C2,A3)

Hi Jeffrey, you have helped me before.

Using the C3 formula gives me a couple of things I'm not sure how to fix including a circular reference.

If I modify it a bit I get the start of the repeating values to occur in the right place (C6), but not the right values (12) and I still get a circular reference.

-Will

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Value	On/Off	Result
2	65		65
3	23		23
4	9		9
5	22		22
6	12	1	12
7	1	1	12
8	4	1	12
9	5	1	12
10	2	1	12
11	10		10
12	2		2
13	5		5
14
Main

Cell Formulas
Range	Formula
C2:C13	C2	=INDEX($A$2:$A$13,ROWS(C$2:C2)-IF(B2=1,SUM(B$1:B1),0))

 

[GeeWhiz7]

How about

+Fluff 1.xlsm
	A	B	C
1	Value	On/Off	Result
2	65		65
3	23		23
4	9		9
5	22		22
6	12	1	12
7	1	1	12
8	4	1	12
9	5	1	12
10	2	1	12
11	10		10
12	2		2
13	5		5
14
Main

Cell Formulas
Range	Formula
C2:C13	C2	=INDEX($A$2:$A$13,ROWS(C$2:C2)-IF(B2=1,SUM(B$1:B1),0))

That works, thank you Fluff and Jeffrey for taking the time. -Will

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Jeffrey Mahoney]

Hi Jeffrey, you have helped me before.

Using the C3 formula gives me a couple of things I'm not sure how to fix including a circular reference.

View attachment 53551

If I modify it a bit I get the start of the repeating values to occur in the right place (C6), but not the right values (12) and I still get a circular reference.
View attachment 53552

-Will

You put the formula in cell C2, not C3

 

[GeeWhiz7]

You put the formula in cell C2, not C3

Ah, okay, that solves the offset. Now I have two ways which work. Thank you.