Sum range updating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
That seems to sum the intire column and I want to always sum is the rows
between 8 and 88.

Chad Wethington
 
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
 
I had tried that, but i thought I would try it again. It still changes the
formula.

Thanks though

Chad Wethington
 
Here is another solution:
=SUM(INDIRECT(C1,TRUE):(INDIRECT(C2,TRUE)))

cell C1 ='worksheet 1'!H8
cell C2 ='worksheet 1'!H88

Hope this helps
 
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
 
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
 
Use SUM with INDIRECT:

=SUM(INDIRECT("'worksheet 1'!H8:H89"))
 
Once I figured out how to set up the inderect comand this solved my issue.

Thanks very much!

Chad Wethington
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
Macro to Sum Two Adjacent Columns as per Text in previous Column 8
SUm function and adding rows 2
DATE RANGE FORMULA 4
Sum Formula with variable range 2
Variable Range 3
sum last 3 numbers greater that 0 in a range 0
Summarize a range referenced in a separate worksheet 3

Back
Top