Selected Answer
Nitil
Please try the attached file (I've lost use of all Office 2016 products for now - due to an update by Microsoft- so using an earlier version where some functions aren't working).
I've made your base data into a table in the sheet Input (so to add a new row, just start typing in the row under the last row). I've set data validation for Strategy 1 to 3 columns to True (or blank) and added the Worksheet_Change event macro below.
If a cell is changed to True within the named table ("Input Table"), it sets cell in the Update Time column to the current time and in Comments, it says which Strategy was adopted at that time (so you could have True in all three columns and know which was the last adopted). It also updates the pivot table values.
Revision 1: The revised code below also gives a "minute-by-minute" report in the sheet "Activity Report". Everytime a Strategy is changed to True in the Input sheet, a new line is added to the report (copying the format from the last one) to show all changes in strategy as time progresses. This is different to your pivot table, which is a snapshot of the current strategies in use at present (but arguably just shows a partial view of the input table).
I note that this report will be daily but leave to rest the Input table (and Activity Report) or write macros to that . Take care to leave at least one data row in the Input Table.
REVISION 2: Code in bold below changed in revised file (since user changed definition of Activity Report)
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("InputTable")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim LastUsed As Integer
If Target.Value = True Then
Cells(Target.Row, 8) = Now
Cells(Target.Row, 9) = "To " & Cells(4, Target.Column)
Sheet2.PivotTables("PivotTable1").RefreshTable
With Sheet1 'update report
LastUsed = .Cells(Rows.Count, 2).End(xlUp).Row ' find last row and copy format to
.Rows(LastUsed).EntireRow.Copy
.Rows(LastUsed + 1).PasteSpecial Paste:=xlPasteFormats
Sheet4.Range("B" & Target.Row & ",E" & Target.Row & ":I" & Target.Row).Copy 'copy cells from Input sheet
.Cells(LastUsed + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
End If
Application.ScreenUpdating = True
End Sub
Note that your pivot table will stay in the order of the left hand column (Name) so if you want the table sorted by Update Time, move that field to the top of the list of Row Labels (so it will be on the left).