Thanks:  0
Likes:  0

1. 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

2. 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.

3. 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.p...ic=567&forum=2

where I provided a short explanation.

Regards,

4. 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

5. 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,

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

7. 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

8. 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.

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

Lindsay

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•