Several issues I'm hoping some smarty pants can help with

I am importing data from a text file that the user will refresh on open.
There are 5 columns to the right of the imported data used for calculated
fields.

There are several tabs that have pivot tables that use the dynamic imported
data on the first tab as its source.

Question 1:
How to have the pivot tables reference a range that will grow/shrink based
on the nbr of rows in the refreshed data AND include the added calculated
columns. I know you can use the Query name as the pivot table source, but it
will not include those 5 calculated columns. Or I can create a name range
that includes the 5 columns, but will not shrink/grow with the refreshed
data. I need to combine the 2 methods somehow....

Question 2:
How can I filter the text data so certain rows are not included in the pivot
table? (I am using average summations and I want to throw out rows that have
zeros for values as there are many and skew the averages incorrectly).

Thanks in advance!
Chad

You can use an offset from the query name to create a dynamic range, e.g.:

=OFFSET(QueryName,,,,COUNTA(Sheet1!$1:$1))

Chad Richardson wrote:
> Several issues I'm hoping some smarty pants can help with
>
> I am importing data from a text file that the user will refresh on open.
> There are 5 columns to the right of the imported data used for calculated
> fields.
>
> There are several tabs that have pivot tables that use the dynamic imported
> data on the first tab as its source.
>
> Question 1:
> How to have the pivot tables reference a range that will grow/shrink based
> on the nbr of rows in the refreshed data AND include the added calculated
> columns. I know you can use the Query name as the pivot table source, but it
> will not include those 5 calculated columns. Or I can create a name range
> that includes the 5 columns, but will not shrink/grow with the refreshed
> data. I need to combine the 2 methods somehow....
>
> Question 2:
> How can I filter the text data so certain rows are not included in the pivot
> table? (I am using average summations and I want to throw out rows that have
> zeros for values as there are many and skew the averages incorrectly).
>
> Thanks in advance!
> Chad
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I use the named range method. The PT source is the range name. Upon adding
new data to the source, I then re-size the named range.

Dim DRow As Long
Dim DRng As Range

DRow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set DRng = Worksheets("Data").Range("A1:G" & DRow)
ws.Names("Database").RefersTo = "=" & DRng.Address(1, 1, xlA1, True)

Hope this helps some
Mike F

"Chad Richardson" <chad@NIX_SPAM_ChadRichardson.com> wrote in message
news:O$(E-Mail Removed)...
> Several issues I'm hoping some smarty pants can help with
>
> I am importing data from a text file that the user will refresh on open.
> There are 5 columns to the right of the imported data used for calculated
> fields.
>
> There are several tabs that have pivot tables that use the dynamic
> imported data on the first tab as its source.
>
> Question 1:
> How to have the pivot tables reference a range that will grow/shrink based
> on the nbr of rows in the refreshed data AND include the added calculated
> columns. I know you can use the Query name as the pivot table source, but
> it will not include those 5 calculated columns. Or I can create a name
> range that includes the 5 columns, but will not shrink/grow with the
> refreshed data. I need to combine the 2 methods somehow....
>
> Question 2:
> How can I filter the text data so certain rows are not included in the
> pivot table? (I am using average summations and I want to throw out rows
> that have zeros for values as there are many and skew the averages
> incorrectly).
>
> Thanks in advance!
> Chad
>