variable cell reference in a macro

[john.hoft-march]

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

 

[Bernie Deitrick]

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

 

[john.hoft-march]

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

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

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

 

[Bernie Deitrick]

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

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

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

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

 

[Pete_UK]

Hi John,

an alternative approach is to use a helper column in your main data and
join the Street, Date and Type data together, eg = street & date &
type, whatever your columns are. Copy this down to the bottom of the
data, and then fix the values using Paste Special. Then copy this
composite column, with a heading, to your second sheet and apply Data |
Filter | Advanced Filter to it to select Unique Records only and Filter
in Place. Then in this second sheet you can apply a COUNTIF formula to
count the number of records of each composite from the main data - you
can then fix your values and you have your "squashed" records - you
could delete the original data and use File | Save As to give this file
a new name.

Hope this helps.

Pete

 

[john.hoft-march]

Just to close out this question, Pete's suggestion worked like a charm,
though my original spreadsheet was so big that replicating formulas (I
used SUMIF to total up the depreciable values of the various sections
of each street) would hang up my computer for up to 10 minutes or so.
Thanks very much.
John

Hi John,

an alternative approach is to use a helper column in your main data and
join the Street, Date and Type data together, eg = street & date &
type, whatever your columns are. Copy this down to the bottom of the
data, and then fix the values using Paste Special. Then copy this
composite column, with a heading, to your second sheet and apply Data |
Filter | Advanced Filter to it to select Unique Records only and Filter
in Place. Then in this second sheet you can apply a COUNTIF formula to
count the number of records of each composite from the main data - you
can then fix your values and you have your "squashed" records - you
could delete the original data and use File | Save As to give this file
a new name.

Hope this helps.

Pete

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 avariablereference so I'll get the total of the
rows preceeding the formula up to the last previous blank row?

Thanks.

John- Hide quoted text -- Show quoted text -

 

[Pete_UK]

Thanks for feeding back, John - I'm glad the method worked for you.

I use this technique on telephone records - combining records which
have the same CLI, call-type, period of call, and duration. Sometimes
there can be 40,000 to 60,000 records, but this can often be reduced to
about 12,000 to 15,000 with the addition of an extra "Count" column.
The "squashed" data fits into smaller files and can be analysed more
quickly, so there are distinct advantages to doing it, even though it
can take a few minutes to squash the data each month.

Pete

Just to close out this question, Pete's suggestion worked like a charm,
though my original spreadsheet was so big that replicating formulas (I
used SUMIF to total up the depreciable values of the various sections
of each street) would hang up my computer for up to 10 minutes or so.
Thanks very much.
John

Hi John,

an alternative approach is to use a helper column in your main data and
join the Street, Date and Type data together, eg = street & date &
type, whatever your columns are. Copy this down to the bottom of the
data, and then fix the values using Paste Special. Then copy this
composite column, with a heading, to your second sheet and apply Data |
Filter | Advanced Filter to it to select Unique Records only and Filter
in Place. Then in this second sheet you can apply a COUNTIF formula to
count the number of records of each composite from the main data - you
can then fix your values and you have your "squashed" records - you
could delete the original data and use File | Save As to give this file
a new name.

Hope this helps.

Pete

On Jan 23, 7:30 pm, (e-mail address removed) wrote:

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 avariablereference so I'll get the total of the
rows preceeding the formula up to the last previous blank row?
Thanks.
John- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -