Author: Oscar Cronquist Article last updated on January 26, 2018

David Hager commentedLooks like an easy pivot table solution to me.

Thanks for your comment! Now i know how to automatically refresh a pivot table when sheet is activated.

Excel 2003 and earlier versions: Create a dynamic named range

This also works in Excel 2007/2010 but it is easier to create a table.

  1. Click "Name Manager"
  2. Create a new named range, named Rng.
  3. Type in "Refers to:" window:
=Data!$A$1:INDEX(Data!$1:$65535, COUNTA(Data!$A:$A), COUNTA(Data!$1:$1))

Click Close button!

Recommended article:

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Excel 2007/2010: Create an excel defined table

  1. Select data range
  2. Click "Insert" tab on the ribbon
  3. Click table
  4. Click "My table has headers"
  5. Click ok

Adding new records to the table is easy. The table expands automatically.

Recommended article:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Create pivot table

  1. Select table
  2. Click "Insert" tab on the ribbon
  3. Click "Pivot table"
  4. Excel 2007/2010: Type table name in "Table/Range" field
    Excel 2003 and earlier versions: Type dynamic named range Rng in "Table/Range" field
  5. Click OK

Recommended article:

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

Setup pivot table

Remember, the original question was how to calculate a running total within a date range and a "client" criterion.

  1. Click and drag Date to "Row Labels" window
  2. Click and drag Client "Report Filter" window
  3. Click and drag Amount to "Values" window

How to auto-refresh pivot table

VBA offers a solution how to automatically refresh pivot table every time you activate "pivot table" sheet.

  1. Right click on the sheet name where you placed the pivot table
  2. Click "View code"
  3. Paste code into code window
  4. Change sheet name and pivot table name in vba code

VBA code:

Private Sub Worksheet_Activate()
    Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

Recommended article:

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

How to select a client

  1. Select "Pivot table" sheet
  2. Click "black arrow" on cell B1
  3. Select a client or multiple clients
  4. Click Ok

See picture below.

Excel 2007/2010: How to select a date range

  1. Select "Pivot table" sheet
  2. Click black arrow on cell B3
  3. Select "Date Filters"
  4. Select a date or dates, a month or months.

See picture below.

Download excel example file

Running totals within date range, pivot table.xlsm
(Excel 2007 Macro-Enabled Workbook *.xlsm)

Running totals within date range, pivot table.xls
(Excel 97-2003 Workbook *.xls)

Recommended blog posts

Interested in learning more about pivot tables? You must read these blog posts!