#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Formula Quesion

I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<"")*ROW(1 4: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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Formula Quesion

JimS formulated on Wednesday :
I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<"")*ROW(1 4: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"))}

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Formula Quesion

On Wed, 10 Aug 2011 10:09:02 -0400, GS wrote:

JimS formulated on Wednesday :
I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<"")*ROW(1 4: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<"")*RO W(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.
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Formula Quesion

JimS brought next idea :
On Wed, 10 Aug 2011 10:09:02 -0400, GS wrote:

JimS formulated on Wednesday :
I have the following formula in cell AR12:

{=SUM(INDIRECT("ar"&LARGE((AR14:AR10000<"")*ROW(1 4: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"))}


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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 86
Default Formula Quesion

GS

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


Gord


On Wed, 10 Aug 2011 23:17:53 -0400, GS wrote:

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.


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.

--
Garry



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Formula Quesion

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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
hopefully a simple solution for Macro quesion hmz Excel Worksheet Functions 3 July 30th 09 08:00 PM


All times are GMT +1. The time now is 11:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"