| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sum range updating
On worksheet 2 I am trying to use the sum function to calculate a range of
cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#2
|
|||
|
|||
|
|
|
#4
|
|||
|
|||
|
That seems to sum the intire column and I want to always sum is the rows
between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#5
|
|||
|
|||
|
Sorry. I don't think you can have the best of both worlds. You could try
naming the range H8:H88 to something like MyRange, then =Sum(MyRange), but if you insert rows, you must insert them within the range for them to stick. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" wrote in message ... That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#6
|
|||
|
|||
|
I had tried that, but i thought I would try it again. It still changes the
formula. Thanks though Chad Wethington "VoG via OfficeKB.com" wrote: =SUM('worksheet 1'!H$8:H$88) -- Message posted via http://www.officekb.com |
|
#7
|
|||
|
|||
|
Can you tell me how to go about naming the range?
Chad Wethington "Anne Troy" wrote: Sorry. I don't think you can have the best of both worlds. You could try naming the range H8:H88 to something like MyRange, then =Sum(MyRange), but if you insert rows, you must insert them within the range for them to stick. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" wrote in message ... That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#8
|
|||
|
|||
|
Here is another solution:
=SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) cell C1 ='worksheet 1'!H8 cell C2 ='worksheet 1'!H88 Hope this helps "Chad Wethington" wrote: On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#9
|
|||
|
|||
|
I have put that formula in, but get #REF!. Here is the exact formula I
entered =SUM(INDIRECT('Sheet Sign & Post Summary'!H8,TRUE):(INDIRECT('Sheet Sign & Post Summary'!H88,TRUE))). Is there a syntax error? Chad Wethington "Odie" wrote: Here is another solution: =SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) cell C1 ='worksheet 1'!H8 cell C2 ='worksheet 1'!H88 Hope this helps "Chad Wethington" wrote: On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#10
|
|||
|
|||
|
INDIRECT looks at the value of cell you specify "C1" and uses that value as a
cell reference So =SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) value of C1 would = 'Sheet Sign & Post Summary'!H8 value of C2 would = 'Sheet Sign & Post Summary'!H88 "Chad Wethington" wrote: I have put that formula in, but get #REF!. Here is the exact formula I entered =SUM(INDIRECT('Sheet Sign & Post Summary'!H8,TRUE):(INDIRECT('Sheet Sign & Post Summary'!H88,TRUE))). Is there a syntax error? Chad Wethington "Odie" wrote: Here is another solution: =SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) cell C1 ='worksheet 1'!H8 cell C2 ='worksheet 1'!H88 Hope this helps "Chad Wethington" wrote: On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#11
|
|||
|
|||
|
Naming ranges:
http://www.officearticles.com/excel/...soft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" wrote in message ... Can you tell me how to go about naming the range? Chad Wethington "Anne Troy" wrote: Sorry. I don't think you can have the best of both worlds. You could try naming the range H8:H88 to something like MyRange, then =Sum(MyRange), but if you insert rows, you must insert them within the range for them to stick. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" wrote in message ... That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#12
|
|||
|
|||
|
Use SUM with INDIRECT:
=SUM(INDIRECT("'worksheet 1'!H8:H89")) Chad Wethington wrote: That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#13
|
|||
|
|||
|
This is exactly what I needed.
Thanks very Much! Chad Wethington "Debra Dalgleish" wrote: Use SUM with INDIRECT: =SUM(INDIRECT("'worksheet 1'!H8:H89")) Chad Wethington wrote: That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#14
|
|||
|
|||
|
Once I figured out how to set up the inderect comand this solved my issue.
Thanks very much! Chad Wethington "Odie" wrote: INDIRECT looks at the value of cell you specify "C1" and uses that value as a cell reference So =SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) value of C1 would = 'Sheet Sign & Post Summary'!H8 value of C2 would = 'Sheet Sign & Post Summary'!H88 "Chad Wethington" wrote: I have put that formula in, but get #REF!. Here is the exact formula I entered =SUM(INDIRECT('Sheet Sign & Post Summary'!H8,TRUE):(INDIRECT('Sheet Sign & Post Summary'!H88,TRUE))). Is there a syntax error? Chad Wethington "Odie" wrote: Here is another solution: =SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE))) cell C1 ='worksheet 1'!H8 cell C2 ='worksheet 1'!H88 Hope this helps "Chad Wethington" wrote: On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington |
|
#15
|
|||
|
|||
|
You're welcome! There's a bit more information in the following MSKB
article: How to force Excel to always reference the same cell http://support.microsoft.com/?kbid=214143 Chad Wethington wrote: This is exactly what I needed. Thanks very Much! Chad Wethington "Debra Dalgleish" wrote: Use SUM with INDIRECT: =SUM(INDIRECT("'worksheet 1'!H8:H89")) Chad Wethington wrote: That seems to sum the intire column and I want to always sum is the rows between 8 and 88. Chad Wethington "Anne Troy" wrote: Chad, why don't you try =SUM('worksheet 1'!H:H) ? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Chad Wethington" <Chad wrote in message ... On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
| Reveal cell formats and extendable range in tool/statusbar/icon. | Excel Worksheet Functions | |||
| Dynamic Print Range Help | Excel Worksheet Functions | |||
| Define a range based on another named range | Excel Worksheet Functions | |||
Linear Mode
