John,

Instead of doing what you are doing, simply select your entire database, and
use a pivot table. (Data / Pivot Table... OK) Then drag "Street", "Type"
and "Year" to the Row field, and drag "Cost" to the data field, set to sum,
and you're pretty much done.

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
I'm trying to condense a very large spreadsheet by summing multiple
entries of the same type and copying that total entry to a second
worksheet. The worksheet is a listing of all of the streets in
Appleton, the year in which work was done to each, and what kind of
paving that work was. There are multiple records for each street
because the information was originally recorded by block. All we need
for our audit is the total for each street by type and year (e.g.
Appleton St., 1975, concrete; not a separate record for each block that
was paved). I've sorted the spreadsheet and recorded a macro that
inserts a couple of blank lines at each change of year or pavement,
copies the last previous line, and inserts a formula to total all of
the amount columns (i.e. length, base value, improvements, accumulated
depreciation, etc.).

My problem is in the formula. I recorded "=sum(up 1 to end-up)" and
got "=sum(R[-4]C,R[-1]C)" ; a fixed range of the preceding 4 cells.
How can I make that a variable reference so I'll get the total of the
rows preceeding the formula up to the last previous blank row?

Thanks.

John