function help

[austin]

Is there a way to reference an group but not reference
part of it? Like if I wanted to have a reference to 40
rows in a column but not the last 8? It seems kinda dumb
but what I want are two seperate references, one that
will reference all but only the last 8 and one that will
reference only the last 8, and when I insert new data,
the previous 8 will become part of the 40 and the new
eight will fill in what was there, it's hard to explain

 

[Anon]

Is there a way to reference an group but not reference
part of it? Like if I wanted to have a reference to 40
rows in a column but not the last 8? It seems kinda dumb
but what I want are two seperate references, one that
will reference all but only the last 8 and one that will
reference only the last 8, and when I insert new data,
the previous 8 will become part of the 40 and the new
eight will fill in what was there, it's hard to explain

It's hard (if not impossible) to understand! I think you should look at the
OFFSET function. This is of the form
OFFSET(reference, rows, cols, height, width)
"reference" is the anchor cell
"rows" and "cols" define how far from the anchor cell your range will start
"height" and "width" define the size of your range
See Help for full details.
As a simple example,
OFFSET(A1,1,2,3,4) is the same as C2:F4

Whilst the following example may well not be what you want, it may help to
show how you might approach your problem. Suppose you kept adding numbers in
column A and you wanted a total of the last 8 cells containing data at any
time. You could use
=SUM(OFFSET($A$1,COUNT(A:A)-8,0,8,1))