Formula to repeat a value

[chipgiii]

I have two sets of months in a row, each column in that row is the next month. The first set of months is Jan thru Dec. The second set is two years of Jan thru Dec.

In the first 12 months only one month will have a value, the rest is zeros. I want a formula that repeats the value of that one month for twelve months in second (two year) 24 months.

Jan Feb Mar Apr May..... Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
50 0 50 50 50 50 50 50 50 50 50 50 50 50 0 0 0
60 0 0 0 0 60 60 60 60 60 60 60 60 60 60 60 60


The month columns are actually dates 1/1/2017 2/1/2017 etc,

Having no luck.

Thanks,
Chip

 

[jtakw]

Hi,

I might be misunderstanding your description, is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	0	0	0	50	0	0	0	0	0	0	0	0	50	50	50	50	50	50	50	50	50	50	50	50
3	0	0	0	0	0	0	0	0	60	0	0	0	60	60	60	60	60	60	60	60	60	60	60	60
Sheet32

Cell Formulas
Range	Formula
M2	=MAX($A2:$L2)

Drag formula down and across as required.

 

[chipgiii]

Hi,

I might be misunderstanding your description, is this what you mean?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]

</tbody>

Sheet32​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=MAX($A2:$L2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Drag formula down and across as required.

I think I figured it out. Your example above is close but the "60" would not start until September and then continue for 12 months.
I ended up with this: =IF(F4=0,I4,IF(AND($D4<=AH$3,EDATE($G4,-1)>=AH$3),MAX($I4:$AF4),0)). The first part "If(F4=0" is to determine whether to start the in put that is to repeat. This is a bit of a nightmare as they are looking at a savings amount and then if it is a one time event, make it one time, other wise spread if over the next 12 months....

Thanks for you interest and help.