HELP --"flexible" formula?

[chezlinds]

I have a formula that I would like to change as rows are added above it. If the formula is in A8 then I want the formula to SUM(A2:A7), but as rows are added I want the formula to account for this (ex./ SUM(A2:A59) if the formula is bumped down to A60). Basically, I want the formula to add up A2 through A"one cell above this position".

Any help GREATLY appreciated!!

Lindsay

 

[Derek]

On 2002-02-26 19:26, Anonymous wrote:

On 2002-02-26 19:22, Anonymous wrote:

On 2002-02-26 19:07, Derek wrote:
Hi
If you still wanted the total below your sum, you could type A2 in cell A1 (or any other cell you choose) and refer to it indirectly like this.

=SUM(INDIRECT($A$1):A7)

Regards
Derek

I don't follow that. The ref A7 in the formula does not change if a row is inserted immediately above row 8.

The formula suggested by Autolycus (entered in A8) automatcally adjusts if row(s) are inserted immediately above the cell with the formula.

Oops! Apologies all, you are right it does not work if a row is inserted immediately above it. I will go and stand in the corner for 10 minutes,
Regards
Derek

 

[Aladin Akyurek]

On 2002-02-26 18:57, Autolycus wrote:
Why not just use :-

=SUM(A2:OFFSET(A8,-1,0))

A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.

 

[Aladin Akyurek]

On 2002-02-26 18:48, anno wrote:
oops. forgot that you can't use a dynamic range if the formula that uses it is in the same column. aladin's is a much better idea, although i don't quite get what the match function is doing.
This message was edited by anno on 2002-02-26 18:51

anno,

There have been a few occasions/discussions at the old board (see Archives) what MATCH is doing in setups similar to the one in this thread & and recently at this board

http://mrexcel.com/board/viewtopic.php?topic=567&forum=2

where I provided a short explanation.

Regards,

Aladin

 

[Guest]

On 2002-02-27 01:35, Aladin Akyurek wrote:

On 2002-02-26 18:57, Autolycus wrote:
Why not just use :-

=SUM(A2:OFFSET(A8,-1,0))

A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.

My close reading implies otherwise. Nowhere does he say that rows will be added below the formula, but rather (the capitals are mine) :-

In his first post "I have a formula that I would like to change as rows are added ABOVE it."

In his second post "If I have sum(A2:A11) in cell A12 and then I insert a row ABOVE row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me." - by "bottom" he obviously means the bottom of the rows being summed, i.e. the row immediately above the formula

 

[Aladin Akyurek]

On 2002-02-27 01:57, Anonymous wrote:

On 2002-02-27 01:35, Aladin Akyurek wrote:

On 2002-02-26 18:57, Autolycus wrote:
Why not just use :-

=SUM(A2:OFFSET(A8,-1,0))

A close reading of Lindsay's post implies that there can be additions/deletions before and after the row of formula, whence I opted for moving the formula to the top in the column of interest.

My close reading implies otherwise. Nowhere does he say that rows will be added below the formula, but rather (the capitals are mine) :-

In his first post "I have a formula that I would like to change as rows are added ABOVE it."

In his second post "If I have sum(A2:A11) in cell A12 and then I insert a row ABOVE row 12, the formula stays sum(A2:A11). If I add rows further up in the spreadsheet, the formula changes like I want, but rows are always added at the bottom, so that doesn't help me." - by "bottom" he obviously means the bottom of the rows being summed, i.e. the row immediately above the formula

No need to defend anything here. My comment had underlying appreciation/approval of the formula Autolycus suggested. My post has/had non-critical intent to communicate, directed to Autolycus, why I chose a different path. Hope you're not going to apply hermeneutics to what I actually wrote and simply accept the correction I just gave.

Regards,

Aladin

 

[Guest]

Apply hermeneutics? What me? Certainly not.
(And not exegesis either.)

 

[chezlinds]

Wow! Thanks for all the input on this problem, everybody. To clarify: rows are added ONLY ABOVE the formula. However, what goes on above the formula is a big, big mess -- additions, deletions, and FREQUENT sorting of the rows are all very common. I am not now in work, but I will try =SUM(A2:OFFSET(A8,-1,0)) when I get in. With this additional information, would this formula seem to work? And, could someone please explain what OFFSET(A8,-1,0) means?

Thanks so much,
Lindsay

 

[Aladin Akyurek]

On 2002-02-27 07:18, chezlinds wrote:
Wow! Thanks for all the input on this problem, everybody. To clarify: rows are added ONLY ABOVE the formula. However, what goes on above the formula is a big, big mess -- additions, deletions, and FREQUENT sorting of the rows are all very common. I am not now in work, but I will try =SUM(A2:OFFSET(A8,-1,0)) when I get in. With this additional information, would this formula seem to work? And, could someone please explain what OFFSET(A8,-1,0) means?

Thanks so much,
Lindsay

The cell immediately above A8.

 

[chezlinds]

UPDATE: The SUM(A2:OFFSET(A8,-1,0)) formula proposed by Autolycus works a treat. Thanks so much everyone.

Lindsay