Dynamic Data Range from External Text File, Filter
 

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

Dynamic Data Range from External Text File, Filter
 

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

Dynamic Data Range from External Text File, Filter
 

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" wrote in message
...
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