+ Reply to Thread
Results 1 to 8 of 8

Formula should retain

  1. #1
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171

    Formula should retain

    Hi,

    In A1 I have formula sum(A3:A12), however when I insert a line in the range,
    the formula changes to sum(A4:A13), however I would like the formula to
    stay the same.

    An idea is sum(Indirect("A3:A12")), however this isnt very flexible.

    Many Thanks

  2. #2
    Stefi
    Guest

    RE: Formula should retain

    Try sum(A$3:A$12)

    Regards,
    Stefi

    „T De Villiers” ezt *rta:

    >
    > Hi,
    >
    > In A1 I have formula sum(A3:A12), however when I insert a line in the
    > range,
    > the formula changes to sum(A4:A13), however I would like the formula to
    >
    > stay the same.
    >
    > An idea is sum(Indirect("A3:A12")), however this isnt very flexible.
    >
    > Many Thanks
    >
    >
    > --
    > T De Villiers
    > ------------------------------------------------------------------------
    > T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
    > View this thread: http://www.excelforum.com/showthread...hreadid=558787
    >
    >


  3. #3
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Ive tried this before, unfortunately doesn't work

  4. #4
    Bob Phillips
    Guest

    Re: Formula should retain

    It isn't, but it is the only way (at least that I know of).

    What specifically is not flexible about it, there may some tweaks to use?

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "T De Villiers" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > Hi,
    >
    > In A1 I have formula sum(A3:A12), however when I insert a line in the
    > range,
    > the formula changes to sum(A4:A13), however I would like the formula to
    >
    > stay the same.
    >
    > An idea is sum(Indirect("A3:A12")), however this isnt very flexible.
    >
    > Many Thanks
    >
    >
    > --
    > T De Villiers
    > ------------------------------------------------------------------------
    > T De Villiers's Profile:

    http://www.excelforum.com/member.php...o&userid=26479
    > View this thread: http://www.excelforum.com/showthread...hreadid=558787
    >




  5. #5
    Paul Lautman
    Guest

    Re: Formula should retain

    T De Villiers wrote:
    > Hi,
    >
    > In A1 I have formula sum(A3:A12), however when I insert a line in the
    > range,
    > the formula changes to sum(A4:A13), however I would like the formula
    > to
    >
    > stay the same.
    >
    > An idea is sum(Indirect("A3:A12")), however this isnt very flexible.
    >
    > Many Thanks


    Are you sure it changes as you said? I would have expected it to change to
    SUM(A3:A13)



  6. #6
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Hi,

    When I mean sum(indirect("a3:a12")) is inflexible, if I copy it across it stays
    as sum(indirect("a3:a12")), as opposed to sum(indirect("b3:b12"))

    Mnay Thanks

  7. #7
    Bob Phillips
    Guest

    Re: Formula should retain

    Thought it might be something like that. Try

    =SUM(OFFSET(INDIRECT(CHAR(64+MIN(COLUMN(A1)))&"3"),,,10,1))

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "T De Villiers" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > Hi,
    >
    > When I mean sum(indirect("a3:a12")) is inflexible, if I copy it across
    > it stays
    > as sum(indirect("a3:a12")), as opposed to sum(indirect("b3:b12"))
    >
    > Mnay Thanks
    >
    >
    > --
    > T De Villiers
    > ------------------------------------------------------------------------
    > T De Villiers's Profile:

    http://www.excelforum.com/member.php...o&userid=26479
    > View this thread: http://www.excelforum.com/showthread...hreadid=558787
    >




  8. #8
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Thanks Bob, thats excellent

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1