Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Vancouver, Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM function does not adjust the bottom anchor (97)

    I am a 1-2-3 user and having difficulty coming up with
    a solution to the way Excel anchors the cells in a range. if I have a formula sum(a1:a3) and this is in A4. If I insert a row between A3 and A4, the formula does not adjust to A1:A4, it stay A1:A3. In 1-2-3 this does adjust. Is there any techniques in Excel to over come this. There was something using Index function but I cannot find it. (I do not want to constantly add blank lines before sum

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function does not adjust the bottom anchor (97)

    Since the row you are inserting is not in the range, the reference is not adjusted. You can use a formula like the one below to get around this:

    <pre>=SUM(A1:OFFSET(A4,-1,0))
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: SUM function does not adjust the bottom anchor (97)

    Hi,

    Another solution to this problem which I have found very convenient is to create a "Relative" Range Name which I usually call 'OneUp' using Insert Name Define, but the trick is to modify the "Refers To" details.

    If your active cell is B5 when you create the Range Name, the "Refers To" details will show "=Sheet1!$B$5". You need to change this to "=Sheet1!B4" taking care to remove the Dollar symbols so that it becomes a Relative range unlike most ranges we use which are Absolute.

    Then you can have your formula as "=sum(a1:OneUp)" and if you insert lines immediately above the Sum your formula will continue to be correct. And because it is relative you can use it on every Sum on a worksheet.

    Good Luck!

Posting Permissions

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