Edit history and version control in Google Sheets - Dashboard Guide

Dashboard Mode - Edit history and version control

Goal

Track changes made to a sales data sheet over time and see the latest version of each entry. This helps understand who changed what and when, ensuring data accuracy and easy recovery of past versions.

Sample Data

Entry ID	Product	Sales	Editor	Edit Date
101	Apples	150	Alice	2024-06-01
101	Apples	160	Bob	2024-06-03
102	Bananas	200	Alice	2024-06-02
103	Cherries	120	Charlie	2024-06-01
103	Cherries	130	Bob	2024-06-04
104	Dates	90	Alice	2024-06-05

Dashboard Components

• Latest Version Table: Shows the most recent edit for each Entry ID.
  Formula for Sales (in row 2, assuming data in A2:E7):
  =FILTER(A2:E7, E2:E7=ARRAYFORMULA(MAXIFS(E2:E7, A2:A7, A2:A7)))
  Note: Since MAXIFS does not work directly for each Entry ID in FILTER, we use a helper formula below.
• Helper Column (F): Latest Edit Date per Entry ID
  In F2:
  =MAXIFS($E$2:$E$7, $A$2:$A$7, A2)
  Drag down to F7.
  This finds the latest edit date for each Entry ID.
• Filtered Latest Rows:
  In G1: "Latest Entries"
  In G2:
  =FILTER(A2:E7, E2:E7=F2:F7)
  This shows only rows where Edit Date matches the latest date per Entry ID.
• KPI Card: Total Latest Sales
  Formula:
  =SUM(INDEX(FILTER(C2:C7, E2:E7=F2:F7), 0))
  This sums sales from the latest versions only.
• KPI Card: Number of Edited Entries
  Formula:
  =COUNTA(UNIQUE(A2:A7))
  Shows how many unique entries have edits.

Dashboard Layout

+----------------------+----------------------+
|   KPI: Total Sales   |  KPI: Edited Entries |
+----------------------+----------------------+
|                                              |
|           Latest Version Table                |
|                                              |
+----------------------------------------------+

Interactivity

Add a filter dropdown for Product. When a product is selected, the Latest Version Table and KPI cards update to show data only for that product. This helps focus on changes and sales for a specific product.

Self Check

If you add a filter for Product = "Cherries", which rows appear in the Latest Version Table and what is the Total Latest Sales?

Answer: Only the row with Entry ID 103 and Edit Date 2024-06-04 (Sales 130) appears. Total Latest Sales is 130.

Key Result

Dashboard shows latest edits per entry with total sales and edit counts, supporting product filtering.