Pivot Tables referring to external data query

[Guest]

In my excel file I am querying external data(an ODBC database) in one sheet.
The query data amounts to over 10,000 rows.

I am using a pivot table to summarize the query data. As my query data
expands, the pivot table range doesn't expand. Is there a setting that
enables the pivot table range to expands with the query data?

 

[Debra Dalgleish]

When the query is imported, a name should be automatically created in
Excel. You can use that as the pivot table source.

To find the name:

Select the sheet that contains the imported data
Right-click on a cell in the imported data range
Choose Data Range Properties, and the range name is at the top
Click OK

Press the F3 key on the keyboard, to open the list of defined names
Look for the imported data name in list. It may be slightly modified,
with underscores replacing the spaces.

Right-click a cell in the Pivot Table
Choose Wizard, and click the Back button
In the data range box, replace the existing reference with the import
data sheet name and range name, e.g.:
ImportSheet!Import_Range_Name
Click Finish

 

[Guest]

Debra,
I understand what you are getting at, makes good sense, but I've added
formulas along side my query data that are outside the range of my query. If
I add a new named rage will it expand when my query is refreshed?

 

[Debra Dalgleish]

You can create your own dynamic range, as in the example shown here:

http://www.contextures.com/xlPivot01.html

In the formula, count both the number of rows, and number of columns, if
they'll both change.

 

[Guest]

Thank you, I appreciate your help.

You can create your own dynamic range, as in the example shown here:

http://www.contextures.com/xlPivot01.html

In the formula, count both the number of rows, and number of columns, if
they'll both change.