Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!* |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Pivot Tables | Excel Discussion (Misc queries) | |||
Updating all pivot tables at once | Excel Worksheet Functions | |||
updating pivot tables | Excel Discussion (Misc queries) | |||
updating tables automatically | Excel Discussion (Misc queries) | |||
updating pivot tables | Excel Discussion (Misc queries) |