Tracking Historic Data

[Ruth]

Hi

I have a spreadsheet which is updated once a month by several other users,
on this spreadsheet i have a summary page which returns me the total number
of staff that need training. What I am looking to do is find a way that excel
will automatically store what the number of staff that need training is at
the beginning of every month. For example I am looking to have a table with
January - December with a numerical value next to each.

I know this seems like a simple fix by just copying and pasting once a month
but ideally i would excel to do it. Is this possible

Thanks in advance for your help

 

[Reeza]

Hi

I have a spreadsheet which is updated once a month by several other users,
on this spreadsheet i have a summary page which returns me the total number
of staff that need training. What I am looking to do is find a way that excel
will automatically store what the number of staff that need training is at
the beginning of every month. For example I am looking to have a table with
January - December with a numerical value next to each.

I know this seems like a simple fix by just copying and pasting once a month
but ideally i would excel to do it. Is this possible

Thanks in advance for your help

How is the data entered? What fields are stored with respect to the
time period?

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Ruth]

I have sent the document over to you now Don hopefully it all makes sense let
me know if you need anything else

I really appreciate any help you can give

 

[Don Guillett]

copies formulas down one row and converts last month to values.

Sub SAS_ConvertFormulasToValues()
mtc = Range("c3").End(xlDown).Offset(, -1)
'MsgBox mtc
With Worksheets(2).Range("b1:b60")
Set c = .Find(What:=mtc, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do

'MsgBox c.Row
If LCase(mtc) = "december" Then Exit Sub
..Cells(c.Row, 2).Resize(, 5).Copy .Cells(c.Row + 1, 2)
..Cells(c.Row, 2).Resize(, 5).Value = _
..Cells(c.Row, 2).Resize(, 5).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub