Writing Habit Tracker Dashboard

What it shows: cumulative words written so far vs target (example: 3067 / 3000). Freezes after goal end date so the card doesn't keep changing.

Formula (recommended, robust):

=LET(
  _Today, $L$6,
  _End,   $L$3,
  _Goal,  $L$5,
  _UseDate, IF(_Today > _End, _End, _Today),
  _Row, IFERROR(MATCH(_UseDate, tblProgress[Date], 0), NA()),
  _Cum, IFERROR(INDEX(tblProgress[CumulWords], _Row), 0),
  TEXT(_Cum, "#,##0") & " / " & TEXT(_Goal, "#,##0")
)

Explanation:

• _UseDate picks today's date but clamps at the goal _End date (freezing display after the period ends).
• MATCH finds the progress row for that date; INDEX(...) returns the cumulative words.
• Text formatting keeps the card tidy and aligned.

Watchouts:

• Make sure tblProgress[Date] contains date-only values (no time component). If not, use =INT(...) or ensure the table stores date-only.
• Add IFERROR so the KPI shows friendly zeros rather than #N/A when no data exists.

What it shows: words written today versus the dynamically recalculated daily target (example: 120 / 150).

Formula (clean):

=LET(
  _Today, $L$6,
  _Start, $L$2,
  _End,   $L$3,
  _RowToday, IF(OR(_Today < _Start, _Today > _End), NA(), IFERROR(MATCH(_Today, tblProgress[Date], 0), NA())),
  _WordsToday, IFNA(INDEX(tblProgress[Words], _RowToday), 0),
  _AvgNeeded, IFNA(INT(INDEX(tblProgress[Avg Daily Needed], _RowToday)), 0),
  IF(_Today > _End, "0 / 0 (goal ended)", _WordsToday & " / " & _AvgNeeded)
)

Notes:

• If the goal period is over it explicitly displays a "goal ended" message.
• Use INT purely for display — store precise averages elsewhere if you need decimals.

What it shows: count of consecutive days with >0 words written (resets on a missed day or when the goal ends).

Formula (readable):

=LET(
  _Today, $L$6,
  _Start, $L$2,
  _End,   $L$3,
  _LastWrite, IFERROR(MAXIFS(tblProgress[Date], tblProgress[Words], ">0", tblProgress[Date], "<="&_Today), NA()),
  _LastZero,  IFERROR(MAXIFS(tblProgress[Date], tblProgress[Words], 0, tblProgress[Date], "<="&_LastWrite), _Start-1),
  _Streak, IFERROR(_LastWrite - _LastZero, 0),
  IF(_Today > _End, "0 (goal ended)", _Streak)
)

Explanation:

• MAXIFS(... "<=" & _Today) finds the most recent positive day and the most recent zero day to compute the run length.
• Result is safe-protected using IFERROR and falls back to zero when necessary.

What it shows: sum of distraction counts for the active goal (1 distraction = ~5 mins stop).

=LET(
  _Goal, $L$1,
  _Today, $L$6,
  _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _Sum, SUMIFS(tblData[DistractionsCnt], tblData[Goal Name], _Goal, tblData[StartDate], ">="&_Start, tblData[StartDate], "<="&_Today),
  IF(_Sum=0,"Great news — no distractions so far!","Total distractions: " & _Sum)
)

Tip: show trend sparkline or % of distraction-days to total-days for richer insight.

What it shows: integer average words/day for the active goal period up to today.

=LET(
  _Goal,$L$1,
  _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _LastDate, IF($L$6 < _Start, _Start, IF($L$6 > $L$3, $L$3, $L$6)),
  _Days, MAX(0, _LastDate - _Start + 1),
  _Total, SUMIFS(tblData[Words], tblData[Goal Name], _Goal, tblData[StartDate], ">="&_Start, tblData[StartDate], "<="&_LastDate),
  _Avg, IF(_Days=0,0,INT(_Total/_Days)),
  "On average, you write" & CHAR(10) & _Avg & " words per day!"
)

Note: use rolling averages (7-day) on a secondary card to capture recent pace changes.

What it shows: rounded mood average with descriptive emoji/comment.

=IFERROR(
  LET(
    _Goal,$L$1,
    _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
    _All, FILTER(tblData[Mood(1-5)], (tblData[Goal Name]=_Goal)*(tblData[StartDate]>=_Start)*(tblData[StartDate]<=$L$6)),
    _Avg, ROUND(AVERAGE(_All),0),
    _Comment, CHOOSE(_Avg,"Very low 😕","Low 🙂","Neutral 😐","Good 😀","Excellent 😃"),
    "Average mood: " & _Avg & CHAR(10) & _Comment
  ),
  "📭 No mood entries yet."
)

Watchout: FILTER errors if no rows exist — that's why the IFERROR wrapper is used.

What they show: the most-used device and most-used location for the active goal (min. 3 samples to be confident).

=LET(
  _Goal,$L$1,
  _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _DevList, FILTER(tblData[Device], (tblData[Goal Name]=_Goal)*(tblData[StartDate]>=_Start)*(tblData[StartDate]<=$L$6)),
  _U, UNIQUE(_DevList),
  _Counts, COUNTIFS(tblData[Device], _U, tblData[Goal Name], _Goal, tblData[StartDate], ">="&_Start, tblData[StartDate], "<="&$L$6),
  _TopCount, MAX(_Counts),
  _Top, IF(_TopCount<3, "More data needed", INDEX(_U, MATCH(_TopCount,_Counts,0))),
  _Top
)

Tip: show count beside the card (e.g., "Desktop PC (8 sessions)").

What it shows: average writing speed (words divided by duration in minutes).

=LET(
  _Goal,$L$1,
  _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _List, FILTER(tblData[WPM], (tblData[Goal Name]=_Goal)*(tblData[StartDate]>=_Start)*(tblData[StartDate]<=$L$6)),
  _Avg, IFERROR(INT(AVERAGE(_List)), 0),
  "Your average writing speed is" & CHAR(10) & _Avg & " words per minute!"
)

Important: ensure duration is stored as numeric minutes — WPM = ROUND(Words / DurationMinutes,1).

What it shows: final status message if complete, otherwise a dynamic projection that estimates completion date at current pace.

=LET(
  _Goal,$L$1,
  _Today,$L$6,
  _Start, INDEX(Goals_Master[Start Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _End,   INDEX(Goals_Master[End Date], MATCH(_Goal, Goals_Master[Goal Name], 0)),
  _WordGoal, INDEX(Goals_Master[Target Words], MATCH(_Goal,Goals_Master[Goal Name],0)),
  _Cum, SUMIFS(tblData[Words], tblData[Goal Name], _Goal, tblData[StartDate], ">="&_Start, tblData[StartDate], "<="&_Today),
  _DaysSoFar, MAX(0, _Today - _Start + 1),
  _Avg, IF(_DaysSoFar=0, 0, INT(_Cum / _DaysSoFar)),
  IF(
    _Today > _End,
    IF(_Cum >= _WordGoal, "✅ Goal complete! You hit " & _Cum & " words!", "⚠️ Goal missed. You wrote " & _Cum & " of " & _WordGoal),
    IF(_Avg=0, "Start writing to get predictions.",
      LET(
        _WordsLeft, MAX(0, _WordGoal - _Cum),
        _DaysNeeded, CEILING(_WordsLeft / _Avg, 1),
        _PredDate, _Today + _DaysNeeded,
        _Delta, _End - _PredDate,
        IF(_Delta>0, "🎉 On track! Finish by " & TEXT(_PredDate,"dd/mmm") & " (" & _Delta & " day(s) early)",
          IF(_Delta=0,"🎯 On track to finish on time: " & TEXT(_End,"dd/mmm"),
            "⏳ Behind. Finish by " & TEXT(_PredDate,"dd/mmm") & " (" & ABS(_Delta) & " day(s) late)"
        ))
      )
    )
  )
)

Notes:

• The projection uses the average words/day so far. For more stability, consider using a 7-day rolling average as an alternative predictor.
• All text output is human-friendly; you can adapt to show structured data for automation.

Formula: =TEXT($L$6,"dd/mmm/yyyy")

Note: $L$6 is your single source of truth for date — useful for reproducible reports by changing that cell manually.

What it does: computes minutes written in each hour (0–23), correctly splitting sessions that span hours or midnight.

Core per-hour minutes (LET version):

=LET(
  hrStart, A2/24,
  hrEnd, (A2+1)/24,
  starts, tblData[StartTime],
  endsRaw, tblData[EndTime],
  ends, endsRaw + (endsRaw < starts),            /* adjust for sessions that cross midnight */
  effStart, IF(starts > hrStart, starts, hrStart),
  effEnd, IF(ends < hrEnd, ends, hrEnd),
  mins, MAX(0,(effEnd - effStart) * 1440),
  SUMPRODUCT((tblData[Goal Name]=$D$1) * mins)
)

Implementation notes:

• Store StartTime and EndTime as actual Excel time/datetime values (not strings). In VBA, write them as Date/Time values (use TimeValue() / CDate()).
• Use the resulting hourly minutes array to derive MAX, INDEX PeakHour, and formatted labels (TEXT(TIME(...),"h:mm AM/PM")).

What it shows: friendly label derived from your peak-writing hour.

=LET(
  _Peak, INDEX($A$2:$A$25, MATCH(MAX($B$2:$B$25), $B$2:$B$25, 0)),
  IF(OR(_Peak>=19, _Peak<5),"Night Owl",
    IF(_Peak>=5 & _Peak<11,"Early Bird",
      IF(_Peak>=11 & _Peak<15,"Noon Lark", "Flamingo")
    )
  )
)

Tip: pair this label with a recommendation (e.g., "Schedule focused sessions at your peak window").

Recommended approach

1. Prefer a Form Controls ComboBox (Developer → Insert → Form Controls → ComboBox) if you want a simple LinkedCell-driven dropdown. It's lighter and works reliably across Excel versions.
2. Use an ActiveX ComboBox only when you need programmatic population, complex formatting, or event handling on the control itself (but ActiveX can be less portable).

Data source (best practice)

Create a table for goals and use that table column as the list. Example:

-- Make Goals_Master a proper Excel Table with a column 'Goal Name'
Named range (optional): GoalList → =Goals_Master[Goal Name]
-- Then set ComboBox ListFillRange = GoalList (or directly Goals_Master[Goal Name])

Form Controls ComboBox properties

• ListFillRange: GoalList (or Goals_Master[Goal Name])
• LinkedCell: 'Daily Progress'!L1 (the single source-of-truth for active goal)
• Style: set to DropDownList to prevent free typing (Forms ComboBox only)

ActiveX ComboBox (if used)

Populate via VBA on workbook open or when Goals_Master changes (example):

Private Sub Workbook_Open()
  Dim arr As Variant
  arr = ThisWorkbook.Sheets("Goals").ListObjects("Goals_Master").ListColumns("Goal Name").DataBodyRange.Value
  With ThisWorkbook.Sheets("Dashboard").cmbGoals
    .Clear
    Dim i As Long
    For i = 1 To UBound(arr, 1)
      .AddItem arr(i, 1)
    Next i
  End With
End Sub

Event: user picks a goal → refresh dashboard

Private Sub cmbGoals_Change()
  ' Write selection into the canonical cell
  Sheets("Daily Progress").Range("L1").Value = Me.cmbGoals.Value
  ' Recalculate and refresh derived ranges/charts
  Call RefreshDashboard
End Sub

Sub RefreshDashboard()
  'Minimal: force recalculation and refresh pivot caches/charts if needed
  Application.CalculateFullRebuild
  ' Optionally refresh pivots:
  ' Dim pc as PivotCache: For Each pc In ThisWorkbook.PivotCaches: pc.Refresh: Next pc
End Sub

Notes & pitfalls

• Avoid volatile formulas to generate the ComboBox list (e.g., heavy FILTER formulas used directly as ListFillRange can cause lag). Use table columns or VBA as above.
• Use LinkedCell as the single source of truth ('Daily Progress'!L1) so all formulas read from one place — easier to debug.
• If you want an "Add goal" UX that immediately appears in the dropdown, call the ComboBox population routine after inserting a new row into Goals_Master.

Why use an ActiveX ListBox

ActiveX ListBox gives fine-grained control of column counts, widths, and can be populated with an array from VBA (fast, avoids volatile ListFillRange issues).

Design-time properties (recommended)

• Name: lstProgress
• ColumnCount: 2 (Date, Words)
• ColumnWidths: e.g. 80 pt; 50 pt
• IntegralHeight: False (so a scrollbar appears)
• MultiSelect: fmMultiSelectSingle (0)

Populate via VBA (fast & reliable)

Use a VBA routine to read the visible / filtered range (the output of your LET+FILTER table) and assign the 2D array to the ListBox:

Sub PopulateProgressListBox()
  Dim wsDP As Worksheet
  Dim rng As Range
  Dim arr As Variant

  Set wsDP = ThisWorkbook.Sheets("Daily Progress")
  ' ProgListRange should be a contiguous 2-col range that contains the date and words (the output of your FILTER+SORT)
  Set rng = wsDP.Range("ProgListRange")          ' e.g. $K$16:$L$100
  If Application.WorksheetFunction.CountA(rng) = 0 Then
    Sheets("Dashboard").lstProgress.Clear
    Exit Sub
  End If

  arr = rng.Value ' arr becomes a 2D array (rows x 2)
  With Sheets("Dashboard").lstProgress
    .Clear
    .ColumnCount = 2
    .List = arr
  End With
End Sub

Call the population routine when

• Workbook opens (Workbook_Open),
• User selects a new goal (cmbGoals_Change),
• New progress is added (call after cmdOK save),
• Or on-demand via a refresh button.

Selecting items / UX

If you want clicking an entry to navigate to the matching row in Daily Progress, use the _Click event to look up the selected date and select that row on the underlying sheet.

Performance tip

Assign the array to .List in one operation — looping AddItem row-by-row is slower on large lists.

Button types

• Form Controls Button — good for portability: assign a macro (right-click → Assign Macro).
• ActiveX CommandButton — offers direct event handlers (cmdOK_Click), but less portable between Excel versions/OS.

Macro examples

Open Add Progress form

Sub OpenAddProgress()
  frmAddProgress.Show vbModal
End Sub

Open Add Goal form

Sub OpenAddGoal()
  frmAddGoal.Show vbModal
End Sub

On Save (from frmAddProgress.cmdOK_Click) — write robust date/time values

Private Sub cmdOK_Click()
  Dim ws As Worksheet, tbl As ListObject, nr As ListRow
  Dim sDate As Date, sTime As Date, eTime As Date, startDT As Date, endDT As Date
  Dim words As Long

  Set ws = ThisWorkbook.Sheets("Data")
  Set tbl = ws.ListObjects("tblData")

  ' -- validate inputs (omitted for brevity) --

  sDate = CDate(txtDate.Value)               ' date
  sTime = TimeValue(txtStartTime.Value)      ' time
  eTime = TimeValue(txtEndTime.Value)        ' time (may be < sTime if crosses midnight)

  startDT = sDate + sTime
  endDT = sDate + eTime
  If endDT < startDT Then endDT = endDT + 1  ' crosses midnight: add 1 day

  words = CLng(txtWords.Value)

  Set nr = tbl.ListRows.Add
  With nr.Range
    .Cells(1, tbl.ListColumns("Goal Name").Index).Value = cboGoalName.Value
    .Cells(1, tbl.ListColumns("Chapter N").Index).Value = CLng(txtChapter.Value)
    .Cells(1, tbl.ListColumns("StartDate").Index).Value = Int(startDT)   ' date-only cell if desired
    .Cells(1, tbl.ListColumns("StartTime").Index).Value = TimeValue(startDT)
    .Cells(1, tbl.ListColumns("EndTime").Index).Value = TimeValue(endDT)
    .Cells(1, tbl.ListColumns("Words").Index).Value = words
    .Cells(1, tbl.ListColumns("Duration (min)").Index).Value = DateDiff("n", startDT, endDT)
    .Cells(1, tbl.ListColumns("WPM").Index).Value = IIf(.Cells(1, tbl.ListColumns("Duration (min)").Index).Value > 0, _
                                                     Round(words / .Cells(1, tbl.ListColumns("Duration (min)").Index).Value, 1), 0)
    ' other columns...
  End With

  ' refresh dashboard artifacts
  Call PopulateProgressListBox
  Call RefreshDashboard

  Unload Me
End Sub

Important rules

• Store times as true Excel serials (Date + Time), not text — otherwise hourly-splitting formulas fail.
• If a session crosses midnight, add 1 day to endDT when endDT < startDT.
• After saving, always repopulate ListBox and recalc charts — do not rely solely on volatile formulas.

Single source of truth

Keep 'Daily Progress'!L1 as the canonical "active goal" cell. All KPI formulas reference it — when controls change this cell, the dashboard updates.

Refresh strategy

1. Repopulate dynamic controls (ListBox/ActiveX Combo) via VBA after data/goal changes.
2. Call Application.CalculateFullRebuild or targeted Range.Calculate where possible.
3. Refresh PivotCaches only if you use pivots.

Accessibility

• Give each control a clear label (use a nearby cell or shape with alt-text).
• Ensure keyboard users can tab the Form Controls (ActiveX focus can be tricky).
• Document the keyboard shortcut to open the Add Progress form (create a macro and assign a shortcut).

Performance tips

• Prefer table columns + VBA population for lists rather than volatile dynamic formulas as ListFillRange.
• Batch UI updates (populate controls first, then call Application.ScreenUpdating = True) to avoid flicker.
• Keep large arrays off worksheets; use arrays in VBA and write results in one go (.List = arr).