Autoupdate Pivot Table for New Range

I have a 50-sheet workbook. The first sheet contains a 
long list (>40,000 rows) and the next 49 sheets each 
contain a pivot table that collects data from the first 
sheet.

Each month the number of rows in the list changes as rows 
are added and deleted. Each month I have to update the 
pivot tables. How can I do this without having to use the 
wizard and manually drag through the new list?


0
9/12/2003 2:55:14 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
501 Views

Similar Articles

[PageSpeed] 3

If you use a dynamic formula to name a range, then use that as the 
source for the pivot table, the new data will be included when the pivot 
table is refreshed.

There are some instructions here:
    http://www.contextures.com/xlPivot01.html#Dynamic

Skeezix wrote:
> I have a 50-sheet workbook. The first sheet contains a 
> long list (>40,000 rows) and the next 49 sheets each 
> contain a pivot table that collects data from the first 
> sheet.
> 
> Each month the number of rows in the list changes as rows 
> are added and deleted. Each month I have to update the 
> pivot tables. How can I do this without having to use the 
> wizard and manually drag through the new list?

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd (439)
9/12/2003 3:08:05 AM
Reply:

Similar Artilces:

Help Gurus! Pivot Table Text as Data
Is there a way to output a Text value in the Data area of a Pivot Table? For example, the pivot table I want is something like this: 5/1 5/2 5/3 5/4 5/5 5/6 task1 brad jeff task2 ann tony mike john task3 clint nick lori task4 tina pat Thanks in advance to everyone. Rafael Rafael, This isn't the purpose of a pivot table. It groups things, and does a calculation for each group (sum, average, etc.). You need to make the table you need yourself. How that's done depen...

Defining a table
Hi I am defining a table using Excel e.g where the headings are regions, customers, etc. How can I move the headings, regions, customers, etc, and shown them on top of the columns instead of the A,B,C columns. Thanks. -- edwino2007 A, B, C, (or if you are running xl in R1C1 format, 1, 2, 3) is how excel essentially works. You really can't. If you don't like it, you could always go under Tools|Options, View tab, and uncheck Row & Column headers. (assuming you are not using xl2007). If you really are a new user, I don't recommend it, as your formulas require Row and Co...

Pivot Table Fix
Yippee, I am so happy, I finally figured out how to trick excel into not showing the blank cell in a pivot table without limiting my data set! In the Pivot table layout move the field from row to page. Then double click on the field and click on the "blank" to hide it. Move the field back to row and hit finish. It seems like you always have to "trick" MS software to do what you want. -- Message posted from http://www.ExcelForum.com ...

linked tables
Hi, I need to copy a linked table to a new table but the new table should not have a link. ie all the data and settings accept for the link to another db. How can I do this? Colm hi Colm, colmkav wrote: > Hi, I need to copy a linked table to a new table but the new table > should not have a link. ie > all the data and settings accept for the link to another db. Use a table creation query, e.g. SELECT * INTO newTable FROM linkedTable mfG --> stefan <-- On 4 May, 11:59, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote: > hi Colm, > > colmkav wrote: &g...

Updating multiple tables using a query driven Form
I have three tables - Staff table - Questions table and a Score table the relationships are Staff to Questions 1 to many; Questions to Score 1 to many when I run the query it does show the junk data I added, When I bring up the Form, it shows the all fields correctly but it will not allow me to add records or update the junk record I added. I want the Score table to be updated/added and to just display the questions and Employee information. Where am I going wrong? Hi Bunky, Many times queries are not updateable - you can't add records to them. In your case, with one-to-many rela...

Checking/making-positive one cell unchecks other cells in range
I have a worksheet with three ranges of 4, 3, and 2 cells on each row. In each range, making one cell "true" means that the other cells are false. The first thing that comes to mind would be putting checkbox controls in each cell; however, there will be 10,000, and possibly more, rows like this making such a solution prohibitive. Thanks in advance. ...

Help 2nd Request with Microsoft AutoUpdate
This is my 2nd request for help. The Microsoft AutoUpdate.app keeps doing its thing downloading and installing the latest Office update. Where or what file does it check to see if you really need the update? The update has been installed and all of the Office apps are working perfect. Again, the problem is the Microsoft AutoUpdate seems to think I need the new Office update. Hello - Are you certain that the update is actually being installed, not simply downloaded? AFAIK, the update may very well self-extract, but it doesn't automatically install. It creates a disk icon on th...

pivot table calculations
I have not done much in pivot tables. But I have designed a pivot table form that does all I need except for the last step - creating a column to show a net amount. That is, I need to subtract……. For beginning leases, I have sums for three types – new, renewal or extension – and a total for all beginning leases. I have the sum of expiring Leases, that’s all I need. I do not care about the type of lease. I need to know a net number for each month – number of all beginning leases minus the total of all expiring leases. I can’t figure out how to get that figure in the pivot ta...

table extends beyond window
I have a table inside a Word doc that extends beyond the right margin of the document. Because of that I cannot see the lower right corner of the table in order to resize it. How can I get it viewable? thanks Ken Click in the table then go to Table> Properties & set the Preferred Width to something that will fit within the margins, adjust from there. HTH |:>) Bob Jones [MVP] Office:Mac On 3/8/10 11:01 AM, in article 42c022c9-0dca-45fa-89ad-eb6c4589993e@y11g2000yqh.googlegroups.com, "krs1105" <krstone@alumni.union.edu> wrote: > I have a tab...

Autoupdating Numbers
bump please anyone... help... : -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1644 View this thread: http://www.excelforum.com/showthread.php?threadid=31411 Your bump post doesn't have any connection with any other post (well, in my newsreader anyway). You may want to post back with your question. And if you didn't get a response, you may want to rephrase it. georgio wrote: > > bump > please anyone... help... :S > > -- > georgio > ---...

excel pivot tables
I posted this same query yesterday, but nobody had an answer. not sure how far you guyts look back so i'll try again! help please Excel 2000 I use a pivot table for a report that has to be sent out in many different formats so I would like to record a macro to do this for me. Part of the formatting that has to be done is changing the page, row and column pivot table fields. I have tried recording a macro, but excel records the cell reference of the pivot table field that i am moving. Obviously the field are not aways ging to be in the same place. Does anyone know the cod...

Pivot Tables
What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, and "Count" in Pivot tables is like "CountA" in formulas. Is this true? I think you have it! It's like what you find in the statusbar, too. RS wrote: > > What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I > am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, ...

New install of Great Plains v8.0
Hi I have just installed a version 8 of great plains however the utilities application is not accepting the sa password for MSSQL 2000. The password is valid. Does anyone know how to fix this? Thanks What message are you seeing? Did you setup a DSN to connect to the Great Plains SQL server? "Jitu" wrote: > Hi > > I have just installed a version 8 of great plains however the utilities > application is not accepting the sa password for MSSQL 2000. The password > is valid. > > Does anyone know how to fix this? > > Thanks > > > s...

Comapring rows of a table to multple rows within the same table
Hi, I have the following data which I have been tasked with identifying a primary case for each customer. Unfortunately the data contains duplicate records such as Case 1 and 2. I would be extremely grateful if you could tell me how can I compare all the rows related to Customer 1 and flag a case as the primary case. CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB 1,1,New, Unassigned, RU, HK 2,1,New, Unassigned, RU, HK 3,1,In Progress, Normal Review, RU, HK 4,1,New, Unassigned, IN, UK 5,2,New, Assigned, JP, 6,2,In Progress, Special Review, BR, HK 7,2,Co...

autoupdate
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel When I open entourage, autoupdate indicates updates available but I must close entourage, I do, and get the same message, close entourage, I cannot install any updates. On 4/22/08 5:10 41PM, in article ee994bf.-1@webcrossing.caR9absDaxw, "pt2393@officeformac.com" <pt2393@officeformac.com> wrote: > When I open entourage, autoupdate indicates updates available but I must close > entourage, I do, and get the same message, close entourage, I cannot install > any updates. Do you check for updates a...

collected related records to populate table
I would like to automate the append/update of a related items table [tblRelatedItems] for our B2B web. I`m not sure how to collect and identify the "related" items to append/update. Our product is books so related items are where the author or series is the same. The details to located related items are in our Inventory Table [tblInventory] Thanks in advance for any help, table details and data sample with results are below. [tblInventory] ID ProdCode Title Author Series Price ReleaseDate Category SubCategory1 SubCategory2 SubCategory3 many more fields not required for this exampl...

Autoupdate 2.1.1 will not launch
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi, when I try to launch the Autoupdate 2.1.1 installer downloaded from officeformac.com I get this error (translated from danish): The Application "AutoUpdate 2.1.1 Update" could not start because the was an error in "&lt;BaseInstaller&gt;&lt;CarbonLib&gt;&lt;CFMPriv_Help&gt;&lt;&gt;" I have the latets updates for OSX installed (10.5.2, 2008-002 v1.0, safari 3.1, etc.) installed. This is the error from the console: Mar 31 12:13:03 TobiiBook [0x0-0x3ad3ad].com.MindVi...

External Data in Pivot Tables
I have a series of files in Excel that I would like to summarize with Pivot Table. The files are too big -- too many lines -- to combine an run the table. Is there a way to link multiple external files into on workbook and run a table? I've explored the external data option, bu I can only get one file with it. Thanks Staci -- Message posted from http://www.ExcelForum.com You could use MS Query to import the data (Data>Import External Data> New Database Query) and use an SQL UNION statement to combine the data into one table. SPenney wrote: > I have a series of files in...

Resolver One: New (and different!) Alternative to Excel
Hello all, I thought you might be interested to hear about a new spreadsheet program that is a new alternative to Excel. Although it uses the familiar interface to enter data and formulae, the underlying model is very different. Data and formulae are turned into code, making it much easier to use a proper programming model within spreadsheets. Spreadsheets are programmable with IronPython, meaning that you can use Python and .NET libraries within them (including putting arbitrary objects in the grid!). http://www.resolversystems.com/ Resolver One is free to use for personal use, and the no...

Linked Table
Good Morning, Does anyone know which format I have to use in excel so when I link it to access it will be converted to memo (Allow Zero Lenght = No)? I have a workbook with several data columns and one of them contens big text which I need to send to access on a daily basis. What is happenning is when I link it to access I am losing party of the text. Thank you very much. Vanessa Simmonds Hi Vanessa, The default registry setting for the "TypeGuessRows" key is 8. Therefore, if your first (8) rows of data contain less than 255 characters, the data type will be Text, thus t...

profile autoupdate
Hi all!!! Win2k, Outlook 2000 workgroup configuration on 40 computers: Next week, we'll change email provider: pop and smtp will change, how do I change server values on all workstations without doing it manually? Thank yuo bye Karim ...

Pivot Table #6
Creating a pivot table but keep getting an error message saying that there are too many rows or columns to drag them off one of them . it has worked in the past when there where only 1556 lines now ther is 4036. I have never had this problem before. Thanks ...

Editing Tables
Hi, I have a database that we use to build some tables each week. There are a bunch of people who use this database. Here is the issue - I dont know a lot about acess but I noticed that if I open one of the tables and then erase a field it does not prompt me to save the table when I close it. This caused an inadvertant table change. Question - is it possible to "protect" the table so that no one can open it up and edit it. Thanks for your help. The first problem is that somewhere along the line, Set Warnings has been turned off either in code or a macro. You need to fi...

End of table
I have several workbooks that were created by someone else. They all have one characteristic that I would like to change. When I create a worksheet, the Ctrl-End key combination jumps to the bottom of the table -- the lower right cell. If my table has 200 rows and 10 columns, Ctrl-End will jump to J200. In these worksheets, Ctlr-End jumps several hundred, and in some cases several thousand, rows beyond the last data cell. I have tried deleting the rows below the last data cell and deleting the cell contents, which appear empty. How can I find out why it is doing this and correct it? Curio...

Business Portal - Adding New User
When I click Add New User in Business Portal; I get the following exception [NullReferenceException: Object reference not set to an instance of an object.] Microsoft.BusinessFramework.Portal.Administration.AC_UserWizard.GetAllDomains() +71 Microsoft.BusinessFramework.Portal.Administration.AC_UserWizard.BuildUserWizardHTML() +3013 Microsoft.BusinessFramework.Portal.Administration.AC_UserWizard.CreateWizard() +22 ASP.businessportal_portal_administration_ac_userwizard_aspx.__Render__control1.... I have tried Repair and Uinstall/Reinstall, but error is same...Please...