| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do you write format results of a function within a function? | Excel Worksheet Functions | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
