can the OFFSET reference be a formula?

[JWolkie]

I've got a formula that adds the 12 most recent item numbers,

=SUM(OFFSET(Y3,-1,-11,1,12))

but I would like the OFFSET(Y3,...) reference to be something like

=MAX(M3:AM3)

so that it automatically adjusts its reference towards the most recent item as I add more to the right of the list. The item number will be the highest, and rightmost, number.

How can I do that?

Thanks!
~Jacob~

 

[just_jon]

Maybe, though not robust

=SUM(INDEX(Y3:IV3,0,MATCH( 9.99999999999999E+307,Y3:IV3)-11):IV3)

 

[JWolkie]

I guess what my question boils down to is:

Is there a way that a formula's cell reference can be replaced by a function (MAX)? Something like

=CELL("address",OFFSET(M3,0,MATCH(MAX(M3:AZ3),M3:AZ3,0)-1))

can return the address of the MAX, but I don't seem to be able to turn it back around into a formula.

 

[Aladin Akyurek]

I guess what my question boils down to is:

Is there a way that a formula's cell reference can be replaced by a function (MAX)? Something like

=CELL("address",OFFSET(M3,0,MATCH(MAX(M3:AZ3),M3:AZ3,0)-1))

can return the address of the MAX, but I don't seem to be able to turn it back around into a formula.

=CELL("address",INDEX(M3:AZ3,MATCH(MAX(M3:AZ3),M3:AZ3,0)))

 

[JWolkie]

> =CELL("address",INDEX(M3:AZ3,MATCH(MAX(M3:AZ3),M3:AZ3,0)))

That gives me the same thing, $Y$3 for example.

How could I get a cell address output, such as $Y$3, to be used as the reference for a formula like

=SUM(OFFSET(Y3,-1,-11,1,12))

?

 

[Aladin Akyurek]

> =CELL("address",INDEX(M3:AZ3,MATCH(MAX(M3:AZ3),M3:AZ3,0)))

That gives me the same thing, $Y$3 for example.

How could I get a cell address output, such as $Y$3, to be used as the reference for a formula like

=SUM(OFFSET(Y3,-1,-11,1,12))

?

=SUM(OFFSET(INDEX(M3:AZ3,MATCH(MAX(M3:AZ3),M3:AZ3,0)),-1,-11,1,12))

 

[JWolkie]

> =SUM(OFFSET(INDEX(M3:AZ3,MATCH(MAX(M3:AZ3),M3:AZ3,0)),-1,-11,1,12))



... A thing of beauty. Thank you!