Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hopefully a simple solution for Macro quesion | Excel Worksheet Functions |