Formula should retain

[T De Villiers]

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

 

[Guest]

Try sum(A$3:A$12)

Regards,
Stefi

„T De Villiers†ezt írta:

 

[Bob Phillips]

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

 

[T De Villiers]

Ive tried this before, unfortunately doesn't work

 

[Paul Lautman]

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)

 

[T De Villiers]

Hi,

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

Mnay Thank

 

[Bob Phillips]

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

 

[T De Villiers]

Thanks Bob, thats excellent