30 Day Summary

[Cary Stueland]

How do I set up a 30 day summary without adding and
deleting lines? I have a six column spreadsheet that
tracks data over the past thirty days (weekends exculded,
I have to delete the last line and insert the first line
every day. There must be an automated way.

 

[Don Guillett]

try this for col A. change to suit.
Sub delandinsert()
Rows(Cells(65536, "a").End(xlUp).Row).Delete
Rows(1).Insert
End Sub

 

[Lance]

Assuming that you just want to in g1 keep a 30 day
running total of column A you could:

=SUM(OFFSET(A1,IF(MATCH(9.99999999999999E+307,A:A)

30,MATCH(9.99999999999999E+307,A:A)-30,0),0,30,1))

In H1, I1 etc you could do column B, C etc for your six
columns of data.

Lance

 

[2rrs]

Something like this will do it.

with data starting in E3;

=SUM(OFFSET(E2,1,0):OFFSET(E2,30,0))

hth, 2rrs