Formula Quesion

[JimS]

I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<>"")*ROW(14:10000),14)&":ar10000"))}

It works exactly as I would like, except for one thing. When I insert
a new column I have to manually change the "ar" and the ":ar10000"
parts of the formula to "as" and ":as10000" to reflect the new column.

Is there a way to have this change automatically when I add a new
column so that I don't have to do it manually?

Thanks

 

[GS]

JimS formulated on Wednesday :

I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<>"")*ROW(14:10000),14)&":ar10000"))}

It works exactly as I would like, except for one thing. When I insert
a new column I have to manually change the "ar" and the ":ar10000"
parts of the formula to "as" and ":as10000" to reflect the new column.

Is there a way to have this change automatically when I add a new
column so that I don't have to do it manually?

Thanks

Your formula uses relative references. Try...

{=SUM(IDIRECT("$AR"&LARGE(($AR14:$AR10000<>"")*ROW(14:10000),14&":$AR10000"))}

 

[JimS]

JimS formulated on Wednesday :

Your formula uses relative references. Try...

{=SUM(IDIRECT("$AR"&LARGE(($AR14:$AR10000<>"")*ROW(14:10000),14&":$AR10000"))}

Hi, and thanks for the response. I tried your formula, and it works,
but just like the original formula if I try adding a column the "$AR"
parts stay "$AR" and it doesn't adjust.

 

[GS]

JimS brought next idea :

Hi, and thanks for the response. I tried your formula, and it works,
but just like the original formula if I try adding a column the "$AR"
parts stay "$AR" and it doesn't adjust.

Well, I can't duplicate that. If I insert columns to the left of ColAR
the formula adjusts to the new position. IOW, if I insert 1 column then
$AR becomes $AS in the formula.

 

[Gord]

GS

How is that possible when "$AR" and ":$AR10000" are text strings?


Gord

 

[GS]

After serious thinking Gord wrote :

GS

How is that possible when "$AR" and ":$AR10000" are text strings?


Gord

I guess it might have something to do with me not testing an array
formula. In a normal formula it works the way I stated!