I am trying to extract data from linked databases spreadsheets using Pivot
tables.
My colleagues and I have constructed databases in Excel 2003 containing more
than 300 fields (which is more than Excel 2003 can handle). To accommodate
this we have split the databases into several worksheets and linked them via
a column of reference numbers. The columns are multiple time based
categories (times 24), and the records (rows) are based on dates. The bulk
of the data is mainly text (from dependent dropdown boxes) with some
numerical data.
We have extracted the data using SumProduct or Autofilter, because
previously we had been unable to get PivotTable to give us meaningful
results.
Recently, as a result of browsing some posts in this forum, and their links,
I have been able to get promising results with PivotTables based on the
reference number column.
My question is this: How do I substitute dates for the reference numbers?
The dates are in a column adjacent to the reference number column in a
different worksheet of the same database.
I am using the €œMultiple consolidation ranges€� with the PivotTable wizard.
I am hoping someone is able to point me in the right direction.
Thank you.

In case someone else has this or a similar problem, I have found a solution
and that is to use Vlookup, or something similar in VBA, to link the
reference numbers to dates in the spreadsheet which contains the dates.

"Normek" wrote:

I am trying to extract data from linked databases spreadsheets using Pivot
tables.
My colleagues and I have constructed databases in Excel 2003 containing more
than 300 fields (which is more than Excel 2003 can handle). To accommodate
this we have split the databases into several worksheets and linked them via
a column of reference numbers. The columns are multiple time based
categories (times 24), and the records (rows) are based on dates. The bulk
of the data is mainly text (from dependent dropdown boxes) with some
numerical data.
We have extracted the data using SumProduct or Autofilter, because
previously we had been unable to get PivotTable to give us meaningful
results.
Recently, as a result of browsing some posts in this forum, and their links,
I have been able to get promising results with PivotTables based on the
reference number column.
My question is this: How do I substitute dates for the reference numbers?
The dates are in a column adjacent to the reference number column in a
different worksheet of the same database.
I am using the €œMultiple consolidation ranges€� with the PivotTable wizard.
I am hoping someone is able to point me in the right direction.
Thank you.