Tracking Historic Data

  • Thread starter Thread starter Ruth
  • Start date Start date
R

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
 
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
Click to expand...

How is the data entered? What fields are stored with respect to the
time period?
 
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.
 
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
 
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
 
Back
Top