Importing ever changing data to be used in a pivot table
Posted by Naomi on April 12, 2000 12:29 PM
I am trying to create a pivot table out of imported data, via a macro. My problem: the data is always changing. There will always be three columns, but the number of rows varies from 50 to 2000. Once the data is imported into Excel, how can I automatically have the range of data selected so that the pivot table wizard recognizes what data to use and doesn't ask me for a range?
THANKS!
Posted by Jen on April 12, 2000 1:16 PM
You could base a pivot table on a named range,
(Insert Menu..Name..Define), and
define the Named Range with an offset formula
so it "knows how many rows deep it is."
The OFFSET does this well. Here's an example:
Definition for "pivot_data_range"
=OFFSET(Data!$A$1:$H$1,0,0,COUNTA(Data!$H:$H))
[put this exact formula into the Named
Range definition, but change the sheet name -
here it is Data]
The 'translation' is as follows:
start with cells a1:h1
[Data!$A$1:$H$1,]
dont move any rows or colums
[0,0,]
the 'depth' of the named range is based
on the number of non-blank rows in column H:
[COUNTA(Data!$H:$H)]
The generic definition for offset is:
OFFSET(reference,rows, cols,height,width)
Remember that this definition relies on all the
cells coming into column H having a value! Its
best to choose a column that will always have
values coming into it. This way, whether you have
10 rows coming in or 10,000 rows, the pivot table
is based on the exact number of variable rows.
