John,

Hiding details is a very powerful part of pivot tables.... can't say
without looking at your data if it will help, but it may. Also, some of your
items can be dragged to the column area, to create columns instead of extra
rows. Turning off subtotals for many of the items can help as well.

If you're still stuck, post a VERY small sample of your data, with all the
columns, and maybe 4 or 5 rows, with a sample of what summary you want - as
part of your message, not as an attachment. People get nervous about
attachments.

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
Thanks for the idea but I haven't been able to make that one work. The
pivot table comes back in a longitudinal format, which makes it twice
as long as the original even though it only shows each type of paving
for each year once for each street. If I add sums for all of the
columns (base value, additions, depreciation, disposals, etc.), it gets
very ugly indeed. Unless there's some better way to do a pivot
table...

John

Bernie Deitrick wrote:
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