Automatically Updating Pivot Tables
 

I have a .txt file I bring into excel automatically, and I want to
automatically update my pivot table size to match what is brought in.

If I turn my data into a List (using excel 2003), it kills the link to my
external data but allows me to update the pivot table automatically when
adding rows to the list.

I am only adding to the rows, not the number of columns. Selecting a huge
range of rows to anticipate future data adds "blank" entries to my pivot
table and screws up the data.

Any ideas? Thanks!

Automatically Updating Pivot Tables
 

Either setup your pivot table to not display "blank" as an option, or create
a dynamic range that the PivotTable can refer to.

Dynamic Range:
Goto the sheet with your data table. Under Insert - Name, Define
Give your range a memorable name (MyRange) and use a formula similar to:
=OFFSET($A$1,0,0,COUNTA($A:$A),NumberOfColumns)

Where NumberOfColumns is the count of how many columns you have in your data
field.

Click Add, then out.

Back in your PivotTable, in the dialogue where you tell it what range to
look at, tell it to look at:
YourWorkbookName.xls!MyRange

The range will now dynamically increase/decrease to match your data.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chally72" wrote:

I have a .txt file I bring into excel automatically, and I want to
automatically update my pivot table size to match what is brought in.

If I turn my data into a List (using excel 2003), it kills the link to my
external data but allows me to update the pivot table automatically when
adding rows to the list.

I am only adding to the rows, not the number of columns. Selecting a huge
range of rows to anticipate future data adds "blank" entries to my pivot
table and screws up the data.

Any ideas? Thanks!

Automatically Updating Pivot Tables
 

Maybe you could use a dynamic range name that grows/contracts with your data:

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic



Chally72 wrote:

I have a .txt file I bring into excel automatically, and I want to
automatically update my pivot table size to match what is brought in.

If I turn my data into a List (using excel 2003), it kills the link to my
external data but allows me to update the pivot table automatically when
adding rows to the list.

I am only adding to the rows, not the number of columns. Selecting a huge
range of rows to anticipate future data adds "blank" entries to my pivot
table and screws up the data.

Any ideas? Thanks!

--

Dave Peterson

Automatically Updating Pivot Tables
 

How do I set it so it does not show blank? I have a dynamic range set up,
thank you both for your replies, but I get blank rows in the data
occasionally that I have to manually delete. I can't for the life of me
figure out how to get rid of blanks.

"Luke M" wrote:

Either setup your pivot table to not display "blank" as an option, or create
a dynamic range that the PivotTable can refer to.

Dynamic Range:
Goto the sheet with your data table. Under Insert - Name, Define
Give your range a memorable name (MyRange) and use a formula similar to:
=OFFSET($A$1,0,0,COUNTA($A:$A),NumberOfColumns)

Where NumberOfColumns is the count of how many columns you have in your data
field.

Click Add, then out.

Back in your PivotTable, in the dialogue where you tell it what range to
look at, tell it to look at:
YourWorkbookName.xls!MyRange

The range will now dynamically increase/decrease to match your data.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*