Dynamic Formula - Insert Value at Certain Index

[Cubist]

I"m having a mental block with this one. In column C, it indicates which index of the data (column F) needs to be changed. Change the current data to the value in column D.

Book1
	A	B	C	D	E	F	G
1	Count	Cummulative Count	Index to insert if only count is more than 1	Value		Current	Expected
2	3	3	3	10,000		30,000	30,000
3	1	4	FALSE	29,000		30,000	30,000
4	1	5	FALSE	5,000		30,000	10,000
5	4	9	9	20,000		30,000	30,000
6	1	10	FALSE	30,000		30,000	30,000
7						30,000	30,000
8						30,000	30,000
9						30,000	30,000
10						30,000	20,000
11						30,000	30,000
Sheet5

Cell Formulas
Range	Formula
B2:B6	B2	=SCAN(0,A2:A6,LAMBDA(a,b,a+b))
C2:C6	C2	=IF(A2:A6>1,B2#)
Dynamic array formulas.

 

[pjmorris]

in G2: =IFERROR(VLOOKUP(ROW()-1,$C$2:$D$6,2,FALSE),F2) and copy down.

HTH

 

[Cubist]

Appreciate your response, but I'm looking for
1 ) A dynamic array formula instead of dragging something down. (I forgot to put it in the body of the thread, but it was in my thread title).
2) The data won't be necessary be in row 2 all the time, so ROW()-1 won't work.

 

[Tetra201]

Try

=IFERROR(VLOOKUP(ROW(G2:G11)-ROW(G2)+1,C2:D6,2,0),30000)

 

[Cubist]

This works. Thanks.

 

[Tetra201]

Glad to have helped.