Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
indirect.ext / pull functions | Excel Discussion (Misc queries) | |||
row-indirect functions | Excel Discussion (Misc queries) | |||
Copying and Filling Cell With Functions | Excel Discussion (Misc queries) | |||
Offset and Indirect functions | Excel Discussion (Misc queries) |