Filling speadsheet with INDIRECT functions?
 

Currently I have a speadsheet (30 columns by 400 rows) that was filled
manually with various (sum, min, max, stdev) INDIRECT formulas. Each row on
my summary sheet computes data from each 20 rows of my raw data. EXAMPLE-
Row 1 ... =sum(INDIRECT("d1:d20")), Row 2 ... =sum(INDIRECT("d21:d40")), Row
3 ... sum(INDIRECT(d41:d60")), etc. Is it possible to "FILL" the cells when
I add rows to the summary sheet? I am now manually typing 28 new formulas
for each new added row. Thanks.

Filling speadsheet with INDIRECT functions?
 

Replace your: =sum(INDIRECT("d1:d20"))
with this: =SUM(OFFSET(D$1,ROWS($1:1)*20-20,,20))
Then just copy it down as far as required.

It'll return what you seek:
Row 1 ... =sum(INDIRECT("d1:d20")),
Row 2 ... =sum(INDIRECT("d21:d40")),
Row 3 ... sum(INDIRECT(d41:d60")),
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"duchem02" wrote:
Currently I have a speadsheet (30 columns by 400 rows) that was filled
manually with various (sum, min, max, stdev) INDIRECT formulas. Each row on
my summary sheet computes data from each 20 rows of my raw data. EXAMPLE-
Row 1 ... =sum(INDIRECT("d1:d20")), Row 2 ... =sum(INDIRECT("d21:d40")), Row
3 ... sum(INDIRECT(d41:d60")), etc. Is it possible to "FILL" the cells when
I add rows to the summary sheet? I am now manually typing 28 new formulas
for each new added row. Thanks.

Filling speadsheet with INDIRECT functions?
 

=sum(INDIRECT("d1:d20"))

Row 1:
=SUM(INDIRECT("D"&row()*20-19&":D"&row()*20))

Edit|Fill|Down
--
John C


"duchem02" wrote:

Currently I have a speadsheet (30 columns by 400 rows) that was filled
manually with various (sum, min, max, stdev) INDIRECT formulas. Each row on
my summary sheet computes data from each 20 rows of my raw data. EXAMPLE-
Row 1 ... =sum(INDIRECT("d1:d20")), Row 2 ... =sum(INDIRECT("d21:d40")), Row
3 ... sum(INDIRECT(d41:d60")), etc. Is it possible to "FILL" the cells when
I add rows to the summary sheet? I am now manually typing 28 new formulas
for each new added row. Thanks.