Updating a range automatecally

[Guest]

Hi there,
I'm trying to perform a sum on a few rows, but when I add a new row I have
to update the range in the sum to have the new row included. Is there a way
to have the range update to include the new row(s)?

Hope that makes sense lol.

Thanks


Ninko

 

[Bernard Liengme]

There are a number of ways:
1) If you have nothing else in the column, us a formula such as =SUM(A:A)
2) If you are using Excel 2003, make the range a List (select range, use
Data | List)
3) Define a dynamic range name - see, for example
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.contextures.com/xlNames01.html
best wishes

 

[Gord Dibben]

Ninko

=SUM(A1:INDEX(A:A,ROW()-1))


Gord Dibben MS Excel MVP

 

[Guest]

Thanks for the help, one other question though. On one colunm I'm using:
=SUM(G:G) but the answer is in cell G5 and so it gives an error, is there a
way of getting around this, like ignore that cell or something?

Sorry I'm not too good with Excel.

Thanks


Ninko

 

[Pete_UK]

You can do:

=SUM(G6:G65536)

Do you really need to include the cells G1:G4 as part of the sum?

Hope this helps.

Pete

 

[Guest]

Sadly yeah, otherwise it will be all over the place.


Ninko

 

[Pete_UK]

Well, okay, you can do this to avoid cell G5:

=SUM(G1:G4)+SUM(G6:G65536)

Hope this helps.

Pete

 

[Stan Brown]

Fri, 13 Jul 2007 16:32:02 -0700 from Ninko

Thanks for the help, one other question though. On one colunm I'm using:
=SUM(G:G) but the answer is in cell G5 and so it gives an error, is there a
way of getting around this, like ignore that cell or something?

Leave G5 blank, and in G6 put this formula
=sum(G1:G5)
Then insert rows above row 5 to add numbers. The formula will update
automatically.

 

[Guest]

Ok, thanks for everyones help.

Take care


Ninko