Recursive Offset ? (Formula Problem )

[alex0182828]

[TABLE="width: 608"]
<tbody>[TR]
[TD]Hello All,

I have a problem that i need to solve it needs an offset or something that i am not able to grasp i am afraid.

I need to populate Column AO with the highest value from AN for that row and all the prior blank rows. In the example below AO is populated with the correct answer.
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Any help would be super, it cant be a macro i am afraid !

Thanks[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 608"]
<tbody>[TR]
[TD]Column AM[/TD]
[TD]Column AN[/TD]
[TD]Column AO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERID111111[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERID121212[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]ORDERID221212[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sancdar]

Would it be possible to replace the blanks with the ORDERID they are tied to, or do they need to stay as blanks?

 

[Marcelo Branco]

If there are no column headers then put a header (text) in row 1 and try this

AM AN AO
[TABLE="width: 175"]
<colgroup><col style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" width="105"> <col style="width: 48pt;" span="2" width="64"> <tbody>[TR]
[TD="class: xl63, width: 105, bgcolor: transparent"]Header1[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Header2[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Header3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ORDERID111111[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ORDERID121212[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ORDERID221212[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]


Formula in AO2
=IF(AM2="","",MAX(AN2:INDEX($AN:$AN,MATCH(REPT("z",255),$AM$1:AM1)+1)))
copy down

M.