Forum Discussion
Automatically replace old data with new data
Hello. I am trying to create a sheet that averages the last 5 scores. My idea is to have the input cell at A1, and the average cell at A7. When a value is entered into A1, it appears in A2. Wh...
I stumbled upon this feed as I was trying to do something similar. On its own it is doing exactly what I want it to however I am trying to get a spreadsheet to work on a slightly larger scale.
I updated the code to keep the 10 most recent data entries instead of 5 easily enough, however I am curious to know is it possible to extend out the code to keep track of and update multiple sets of data in the one spreadsheet say a column for every student in a class?
If I had a class of 25 students could I set it up to keep track of all of their most recent results?
biza_j With a setup like this, where the new scores are input in row 2...
Multi-column setup
...you could try the following modified code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Stop the macro if more than one cell was changed
If Target.Cells.Count > 1 Then Exit Sub
'Verify an input cell in row 2 was changed and is not blank
Dim rg As Range, lastCol As Long
lastCol = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column
Set rg = Me.Range("A2").Resize(, lastCol)
If Not Intersect(Target, rg) Is Nothing And Target.Value <> "" Then
' validate the new score
If Not Application.WorksheetFunction.IsNumber(Target.Value) Then
' display validation error
MsgBox "Please enter numeric values only", vbCritical, "Invalid Score"
Else
' update the last 10 scores
Set rg = Target.Resize(10)
rg.Offset(1).Value = rg.Value
' clear the input cell
Target.ClearContents
End If
Target.Select
End If
End SubThank you,
The first column is still replacing data after the tenth entry, but this is not extending to columns 2 on wards.
Made the adjustment to enter data into row 2 instead of row 1 unless i am messing something else up.
biza_j Interesting... I don't have that problem with the code and setup I shared. There were a number of little changes I made to the code from my original post. If you didn't copy and paste the updated code in its entirety, but rather made individual edits to your own existing code, there may have been one or two changes that were missed.
Please see the updated workbook below, if needed...
No I have used your file and started inputting data.
the first column works for 10+ entries of data, the second column works for 8 data entries then stops clearing row 2 and all other columns after don't add the data to the the cells below at all.