I am only a beginner so could you please keep it simple.
I need to build a spreadsheet for weekly reporting.
I need one column for "number installed to date".
I need another column for "number installed this period" which will add onto the "number installed to date".
Every week I will have to zero out the "number installed this period column", without changing the values in the "number installed to date" column.
Once zeroed, I need to enter new figures for the week, which ideally would add themselves to "number installed to date".
Any help would be much appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

I hope this code will be self-explanatory enough. If not, feel free to ask
more questions. Basically it takes the value in the 'number installed to
date' cells and keeps the value and rebuilds the formula to add that value as
a part of the new formula.

Example, let's assume that column A has the 'installed this period' numbers
starting at row 2 and in column B is the 'number installed to date' formula,
which might start out initially looking like this:
=A2 + 0
and you put 7 in A2, so B2 shows 7. After running this code the formula in
B2 would be:
=A2 + 7
and A2 will be set to zero.

But to start things off, if you don't want to put in the =A2+0 formula, you
can simply put in =A2 to initialize things and get a value to build on, but
it does need some value in column B to use as the basis of the new formulas.

Sub PrepareForNewPeriod()
'you must have the sheet with the
'formulas and values in it selected
'before running this macro
' jlatham
'
Const PeriodColumn = "A" ' installed this period
Const ToDateColumn = "B" ' total to date values
Const FirstDataRow = 2 '1st row with formula
Dim cOffset As Integer
Dim lastRow As Long
Dim anyFormula As String
Dim rOffset As Long
Dim baseCell As Range

'calculate offset from
'ToDateColumn to PeriodColumn
cOffset = Range(PeriodColumn & 1).Column - _
Range(ToDateColumn & 1).Column
'find last row to change
'formulas in
If Val(Right(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
lastRow = Range(ToDateColumn & _
Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
lastRow = Range(ToDateColumn & _
Rows.CountLarge).End(xlUp).Row
End If
'set up a base cell location as the
'first cell with a 'to date' value/formula in it
Set baseCell = Range(ToDateColumn & FirstDataRow)
Do Until baseCell.Offset(rOffset, 0).Row lastRow
'build new formula
baseCell.Offset(rOffset, 0).FormulaR1C1 = _
"=RC[" & cOffset & "] + " & _
baseCell.Offset(rOffset, 0).Value
' zero out period entries
baseCell.Offset(rOffset, cOffset) = 0
' to next row
rOffset = rOffset + 1
Loop
End Sub

"Pieter" wrote:

I am only a beginner so could you please keep it simple.
I need to build a spreadsheet for weekly reporting.
I need one column for "number installed to date".
I need another column for "number installed this period" which will add onto the "number installed to date".
Every week I will have to zero out the "number installed this period column", without changing the values in the "number installed to date" column.
Once zeroed, I need to enter new figures for the week, which ideally would add themselves to "number installed to date".
Any help would be much appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Hi Pieter,

I'm curious as to why you would want to 'zero out' your weekly input.
Surely it would be better to save your input as a permanent record.
Excel offers many ways to analyse and report your raw data.

If this is along the lines of what you are trying to create please
post back with a detailed explanation of what your data is,
how you wish to input your data, and what output you wish to achieve.

HTH
Martin


<Pieter wrote in message ...
I am only a beginner so could you please keep it simple.
I need to build a spreadsheet for weekly reporting.
I need one column for "number installed to date".
I need another column for "number installed this period" which will add
onto the "number installed to date".
Every week I will have to zero out the "number installed this period
column", without changing the values in the "number installed to date"
column.
Once zeroed, I need to enter new figures for the week, which ideally would
add themselves to "number installed to date".
Any help would be much appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Peiter.

With column headers in Row 1 of Columns A:D of:
A1: "Date"
B1: "Number Installed"
C1: "Number This Week"
D1: "Total Installed"

In C2 eneter the formula
=SUMPRODUCT((INT(($A$2:A2-1)/7)=INT((A2-1)/7))*$B$2:B2)

and copy doen using the fill handle.

Then in D1 enter:
=SUM($B$2:B2)

and again copy down using the fill handle.

This assumes that your weeks run from Sunday through to Saturday and you
want the "Number This Week" to reset on the first day of a new week
regardless of what day that is. The "Total Instlled will continue to add up
regardless of the resetting of "Number This week" because it does not
reference that column.

As both JLatham & MartinW said, do post back if you need any further help.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


<Pieter wrote in message ...
I am only a beginner so could you please keep it simple.
I need to build a spreadsheet for weekly reporting.
I need one column for "number installed to date".
I need another column for "number installed this period" which will add
onto the "number installed to date".
Every week I will have to zero out the "number installed this period
column", without changing the values in the "number installed to date"
column.
Once zeroed, I need to enter new figures for the week, which ideally would
add themselves to "number installed to date".
Any help would be much appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com