SUM Function
 

How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

SUM Function
 

Try this
SUM($B$1:$B$6)


"Shawn" wrote:

How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

SUM Function
 

They still moved....

"Mike" wrote:

Try this
SUM($B$1:$B$6)


"Shawn" wrote:

How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

SUM Function
 

Perhaps this will work:

=SUM(INDIRECT(ADDRESS(1,2)&":"&ADDRESS(6,2)))

HTH,
Elkar


"Shawn" wrote:

They still moved....

"Mike" wrote:

Try this
SUM($B$1:$B$6)


"Shawn" wrote:

How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

SUM Function
 

=SUM(INDIRECT("B1"):INDIRECT("B6"))
--
David Biddulph

"Shawn" wrote in message
...
How do I stop the function from moving if I insert cells? For example, if
my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

SUM Function
 

"Shawn" wrote...
How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

An obvious question is why would you insert cells in this range when you
know you have formulas referring to it? There are often better approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate every
time anything triggers recalc. If you have lots of such formulas, it could
noticeably slow recalculation.

SUM Function
 

BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)) )

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" wrote in message
...
"Shawn" wrote...
How do I stop the function from moving if I insert cells? For example, if
my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

An obvious question is why would you insert cells in this range when you
know you have formulas referring to it? There are often better approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate every
time anything triggers recalc. If you have lots of such formulas, it could
noticeably slow recalculation.

SUM Function
 

Why not

=SUM(INDEX(B:B,1):INDEX(B:B,6))



"Ragdyer" wrote:

BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)) )

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" wrote in message
...
"Shawn" wrote...
How do I stop the function from moving if I insert cells? For example, if
my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

An obvious question is why would you insert cells in this range when you
know you have formulas referring to it? There are often better approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate every
time anything triggers recalc. If you have lots of such formulas, it could
noticeably slow recalculation.

SUM Function
 

Why not?

Simple ... I just didn't think about it!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JMB" wrote in message
...
Why not

=SUM(INDEX(B:B,1):INDEX(B:B,6))



"Ragdyer" wrote:

BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)) )

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" wrote in message
...
"Shawn" wrote...
How do I stop the function from moving if I insert cells? For example,
if
my
function is SUM(B1:B6) and I insert cells it changes it to
SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

An obvious question is why would you insert cells in this range when
you
know you have formulas referring to it? There are often better
approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate
every
time anything triggers recalc. If you have lots of such formulas, it
could
noticeably slow recalculation.

SUM Function
 

That worked, thanks!!

"JMB" wrote:

Why not

=SUM(INDEX(B:B,1):INDEX(B:B,6))



"Ragdyer" wrote:

BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)) )

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Harlan Grove" wrote in message
...
"Shawn" wrote...
How do I stop the function from moving if I insert cells? For example, if
my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)

An obvious question is why would you insert cells in this range when you
know you have formulas referring to it? There are often better approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate every
time anything triggers recalc. If you have lots of such formulas, it could
noticeably slow recalculation.