In B1 I have a formula: sum(B3:B9),
if i now insert a row in B3 the formula changes to sum(B4:B10),
how do I keep the formula as sum(B3:B9) when inserting the line.

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=554452

Nel post news:T.De.Villiers.29sqqz_1150967707.2076@excelfor um-nospam.com
*T De Villiers* ha scritto:

In B1 I have a formula: sum(B3:B9),
if i now insert a row in B3 the formula changes to sum(B4:B10),
how do I keep the formula as sum(B3:B9) when inserting the line.

Thanks


Hi,

try this way:

=SUM(INDIRECT("B3:B9"))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Not sure what you're after here - do you want the formula to include the
line you've inserted, skip it or ignore it?
If you don't want the formula to include the inserted line, then you
should either insert it below the limit of the fornula or (more
complicated) edit the formula to skip the inserted line - something
like sum(b3:b6)+sum(b8:b10) thereby skipping row 7. There may be a more
elegant way of getting around your problem, but unless you HAVE to
insert a line in the middle of a series that are being summed why do
so?

Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=554452

=SUM(OFFSET(B1,2,0,7,1))

--
HTH

Bob Phillips

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

"T De Villiers"
wrote in message
news:T.De.Villiers.29sqqz_1150967707.2076@excelfor um-nospam.com...

In B1 I have a formula: sum(B3:B9),
if i now insert a row in B3 the formula changes to sum(B4:B10),
how do I keep the formula as sum(B3:B9) when inserting the line.

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=554452