Formula to include inserted cell range

**gandolff** · 10-12-2009, 02:36 AM

Hi,

OK, to simplify things, let's say I always have =SUM(A2:A5) in A1. I need to periodically "insert copied cells down" into A2 from somewhere else -- i.e. I want =SUM(A2:A5) to INCLUDE the newly inserted cells in its calcuation as well as the ones it already did include. Only thing is, whenever I do this, say by inserting two cells into A2, =SUM(A2:A5) "shifts" down and looks like this: "=SUM(A4:A7)". That is, it includes the bottom cell just fine (A7), but totally ignores the two newly inserted ones, A2 & A3. I've tried the whole absolute "$" thing out the wazu, but nothing seems to work... Anyone here know how to resolve this?

Thanx a bunch,
G

**ConneXionLost** · 10-12-2009, 02:56 AM

Hi gandolff,

You may want to check out Dynamic Named Ranges, and the use of the OFFSET function.

Cheers,

**DonkeyOte** · 10-12-2009, 03:55 AM

Given you say you always insert into A2 then use:

=SUM(INDEX(A:A,2):A5)

The A5 reference will alter accordingly however the commencement of the range will always be A2 regardless.