RE: Excel

Hi Ian

There is a way of running a pivot table where there are a variable number of rows of data added.

To do it you have to use a range name with a dynamic formula to define the source range for pivot table. Then as new items are added to the table, the named range will automatically expand.

Start by clicking in any cell in the range of data then
1. Select Formulas, Define Name.
2. Type a range name eg Database.
3. In the Refers to box type the following:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),N)

where Data is the name of the worksheet containing the database.

4. Replace N with the number of columns in the Database range.

Then when creating the Pivot table

1. Select Insert, Pivot Table.
2. In the Select a table or range box type Database.
3. Cick OK to place the pivot table into a new worksheet.

Now whenever new records are added all that's needed is to select Povot Table Options, Refresh.

If there is likely to be a variable number of columns then replace N by:
CountA(Data!$1:$1)

This method avoids the need for macros or VBA code.

Regards
Doug Dunn
Best STL