Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using external query (mdb) for dynamic date range | Excel Worksheet Functions | |||
Dynamic named range reference from external workbook | Excel Discussion (Misc queries) | |||
Set external cell filter for a range | Excel Discussion (Misc queries) | |||
appending data from an external text file | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |