Referencing Data in a Pivot Table

I have created a pivot table which shows me total money spent in various accounting categories.  I want to reference the sums this creates to subtract them from a total budget to give me a remaining total.  Unfortunately, as the pivot table is updated, cell references change.  This means that the total I used as say total spent on fuel may become total spent on salary.

Can anyone help?
0
1/21/2004 10:11:03 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
822 Views

Similar Articles

[PageSpeed] 8

You can use the GETPIVOTDATA function to extract data. Look in Excel's 
help for information and examples for your version of Excel.

Brian wrote:
> I have created a pivot table which shows me total money spent in various accounting categories.  I want to reference the sums this creates to subtract them from a total budget to give me a remaining total.  Unfortunately, as the pivot table is updated, cell references change.  This means that the total I used as say total spent on fuel may become total spent on salary.
> 
> Can anyone help?


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

0
dsd1 (5911)
1/21/2004 11:17:08 AM
Reply:

Similar Artilces:

Data Tables in Charts
Is there any way to total rows and/or columns in the Data Tables tha can be added to Pivot Charts (Table Options)?? I can obviously get th totals in the Pivot Tables but was hoping to have the totals display o the Data Tables also. Any ideas??: -- Monica_Dale ----------------------------------------------------------------------- Monica_Daley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3521 View this thread: http://www.excelforum.com/showthread.php?threadid=54979 ...

comparing data #2
I was wondering is there a way to compare data from 2 columns in a side by side comparison. I have a large list of numbers and what I want to do is copy another list of numbers into the spreadsheet. But I want the numbers in cell A1 and B1 to be equal if they are is not a match then leave cell b1 empty. I want that to continue all the way through the entire worksheet. Maurice, Do you want to end up with 2 separate columns, or are you wanting to merge the 2 lists into 1 list, without duplications? Dave I want 2 side by side columns. With the numbers that are equal right next to each othe...

Protect Data, but allow sorting
Hello, In Excel 2003 is it possible to protect the data in a sheet, but to allow the data to be sorted? In the Protect Sheet command, it indicates that this facility exists, but when I try to do a sort it won't do it until the sheet is unprotected. Pete Make sure that there's an empty column between the range to be sorted and any "adjacent" data. If you have a locked cell butting up to the range to be sorted, excel yells! Pete Walburn wrote: > > Hello, > > In Excel 2003 is it possible to protect the data in a sheet, but to allow > the data to be so...

External Data from Secured Access Application...
I have a secured access application with a query that I want to use as the source for an excel chart. When I attempt to use anything out of this Access application (even a raw table), the MS Query window won't expand to show the field(colums). Can anyone help me get this working??? Kathy ...

How to get Preview Pane in separate Data File in Outlook 2002
For back-up and portability purposes, we use separate Outlook Data Files. (File, New, Outlook Data File, ....) Then we use the Organizer Wizard to move files from the Inbox to the appropriate data file. All of this works great. The only thing we can't figure out is how to turn on the preview pane on one of these files. It works fine on the standard InBox or any Personal Folder that is in the main *.PST file. We use the described method (View, Current View, Other Settings, check by Preview Pane) and we don't get any error message. However, we also do NOT get ...

Linked Table Manager
I am using the following code to launch the Linked Table Manager in Access 2007: DoCmd.RunCommand acCmdLinkedTableManager My question is: Is there a simple way to tell if the linking was Successful, Failed, or if the user Canceled the process? Thanks in advance Mark -- Message posted via http://www.accessmonster.com Your code simply open the Linked Table Manager console. It will return messages based on the process. It will tell you if it was successful or not. The only other option would be to create your own table linking form/routine. -- Hope this helps, D...

Migrating data from SalesLogix to MSCRM?
Hi There, Has any one done migrating of data from SalesLogix to MSCRM? We are using Scribe to migrate data, but Scribe has to us that making use of Scribe adaptor would not make much difference since our version of SalesLogix is 5.x. So we are connecting to SaleLogix database using ODBC which is fine. We just want to know any one has been through this experience and give us some helpful information. In particular how we could bring across activites from SalesLogix to MSCRM, since we have limited knowledge of SalesLogix data structure. Regards, Kyaw The SLX data structure is reall...

Update multiselect field in table with code
Access 2007 under Vista Using a form to select variables for a report. One of the fields is a multi-value field in the underlying table. Have determined that following the selection of required items from the multi-select combo box and the clicking of the OK button, the underlying table is not immediately updated. The table is however properly update once the form is closed. This does not meet my purposes for the report generation, as exiting the form and returning is not a reasonable approach. I would like to use the After Update event from the multi-select combo box to ac...

Ignore a Blank Data Series in the Legend
Hi, I'm creating Pie Charts based on data from 10 data series fields. Some of the data fields are blank, and so I've managed to create data labels that only show on the Pie Chart if there is a value (otherwise the Chart was too hard to read due to lots of blank labels). However, if I show the Legend, the blank data series field still appear, so I have say, 4 labels with the correct data series names but 6 which are blank. Can I make the Legend ignore blank fields, and just show the 4 that have values? Thanks You can manually remove individual legend entries. Click on the lege...

Extract unique data
I am using excel 2003 I have a file like below Rate month GBP 1.46 FEB 2000 1.47 MAR 3000 1.47 APR 3500 1.48 MAY 1000 I want to create summary Rate FEB MAR APR MAY 1.46 2000 1.47 3000 3500 1.48 1000 I can use sumif function to return the total, however, how can I insert only the unique rate in the first column automatically. Thanks a lot ! eva cheng Hi, I suppose information is in sheet 1 columns A,B...

Data validation doesn't work if contents is added by pasting
Hi I've data validation set so that only the number 1 can be entered, but even this cannot be entered if an adjacent cell is a certain value. this works for direct entry, but not if the value is pasted in or added using the "drag handle". Is there any way to stop this happening as I just know somewones going to try and fill all the cells by draging the values down. Regards Jeff Jeff, You could disable "Allow cell drag and drop" in Tools | Options or... copy the following code, right click the sheet tab and choose 'view code'. Paste the code into the modu...

Fixed Assets ACRS tables
Is there a way to update/change ACRS tables? Can new ones be added? Our client here in sydney is reporting to their US headquarters, and they are using depreciation rates table from Gap 20. The rates are not on the ACRS tables of GP. Thanks. ...

Cannot copy table from Excel 2007 to Word 2007 w invisible grid li
How do I copy a table from Excel 2007 to Word 2007 with invisible grid lines? In previous version of Word, it was possible to do so by clicking on "insert special" and choose one of the categories (I have forgotten which). With Word 2007, the grid lines become visible no matter which option I choose under "insert special". Is it possible for you to help me? Thanks! To hide the gridlines, click the View Gridlines button (to deselect the option) on the Table Tools Layout tab. This setting will be preserved between Word sessions. If you are in fact referr...

Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a line chart from a data table, then move the plot points on the chart to "smooth" the line, and the source data chart value would change with the corresponding new plotted point. I'd like to use that capability with Excel 2007 but can't find to activate it. Any power users know how? Thanks! Ed Ed, Microsoft decided that users didn't need that feature. More than likely, they were tired of support calls asking why the values changed when one dragged a data point...... No really, they took i...

Working in Sheet2 with data from Sheet1
I am working in Sheet2. The data is in Sheet1. I can not get the data in column in the 15th column to initalize the "If" even if Textbox1.Text matches. For k= 1 to 100 If Sheets("Sheet1").Cells(k,15)=TextBox1.Text Then code... End if Next k I tried: If Sheets("Sheet1").Cells(k,15)=Sheets("Sheet2").TextBox1.Text Then With Sheets("Sheet2") If Sheets("Sheet1").Cells(k,15)=.TextBox1.Text Then .... End With If Sheets("Sheet1").Cells(k,15)=TextBox1.Text Then Nothing worked. Philos...

Where to get Data Migration Framework 3.0
Can someone tell me where to download the Data Migration Framework version that works with MS CRM 3.0? Thanks in advance Dear Samuele, "Samuele Marconcini" wrote: > Can someone tell me where to download the Data Migration > Framework version that works with MS CRM 3.0? See: http://groups.google.com/group/microsoft.public.crm/browse_thread/thread/dfb1fef18647c6e2/ Cheers Arne Janning Thanks Arne. ...

Pivot table bottom ten filter with conditions
Hi is it possible to have the bottom ten item of a pivot table but excluding specific value(s) eg I have a pivot table with the bottom 10 of cost item 1 -200 $ item 2 -300$ item 3 -0$ .... I would like to exclude item that have a cost = 0$ and have only items greater than 0. thanks Excel 2007 PivotTable Bottom 10, no zeros. Three ways. http://www.mediafire.com/file/kd3dydo3ww4/01_31_10.xlsx thank you for the spreadsheet. ...

Multiple table lookup
How do I set the LOOKUP formulas for this situation: TABLE 1: * Column A displays the names of salesmen (10) * Cells B1:F1 display the names of Products (5) * In cells B2:F11 are displayed the % of budget achieved by each salesman for each of the 5 products TABLE 2: In a separate table I set up a commission matrix showing the % commission due for each product according to the % of budget achieved; the names of the products run across the top, the % achieved vs. budget in column A, and the % commission payable in the intersecting cells TABLE 3: Contains the lookup formulas that calcul...

Using non-numerical imported data
When importing data from financial websites, some of it comes in as 171.1Mil instead of 171.1 , which means Excel won't recognise it as a figure and I can't manipulate it. Any suggestions? thanks Hi You could use Data/Text to columns to split off the text bit, or use =--LEFT(E14,LEN(E14)-3) to trim it off. Andy. "Ricardinho" <anonymous@discussions.microsoft.com> wrote in message news:0acc01c39309$c0a93810$a401280a@phx.gbl... > When importing data from financial websites, some of it > comes in as 171.1Mil instead of 171.1 , which means Excel > won't r...

Pivot Table percentages #2
I have created a pivot table that is running off some data that looks like this (simplified version): Name Accept Decline Joe 1 0 Mary 1 0 Fred 0 1 The pivot table then counts these columns to give the total number of accepts and declines (on 2 seperate lines). Is there a way within the pivot table to represent the declines as a percentage of the accepts? I've tried using the % of Row and % of Column, but I can't find a way of doing it as a percentage of a different column/row. I have...

Delete Pivot Table Formula
How do i delete calculated fields from pivot table field lists? Thanks, sduffield Please check your previous post of this question... -- HTH... Jim Thomlinson "sduffield" wrote: > How do i delete calculated fields from pivot table field lists? > > Thanks, > sduffield ...

Unable to Edit Lookup Tables (Error: 'ExcelBtn' is undefined)
Project Server 2007 SP2 A week or so ago, we were happily adding/modifying Lookup Tables...but today when I click on an existing lookup table to modify (or select the 'New Lookup Table' button) I received a mostly blank screen. Strange thing is, when I'm trying to edit an existing lookup table the resulting screen says "New Lookup Table", some empty frames are barely visible. Clicking on the details of the accompanying error message: Line: 1366 Char: 8 Error: 'ExcelBtn' is undefined Code: 0 I can use the copy lookup table feature successful...

Insidious Plot to Normalize Data!! Bwahahaha!!
Hi folks! Sorry for the melodramatic introduction... Here's the thing: I've got a database that relies heavily on a non normalized table. The structure is like this: Order Number is the primary key and there are about 30 fields with information about the particular order that are filled in by Order Entry personnel. There are four fields (and related fields) for entering information about up to four different part numbers per order. I would like to normalize this table with a union query that John Spencer helped me develop so that I can split off the part numbers to anothe...

forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view. All the tables, queries, and macros are there. When I open up the database to add data, all the forms are there and can be used. The data is stored. So, it seems that the forms are still there, but just cannot be seen in design view. The database, when I open it up in design view, says Access 2000 file object even though I am using Access 2003 - could that be the problem? Thanks! You cannot open an entire database in Design View. Try opening it normally, selecting a form, and choosing Design View from the V...

Count invalid data entries
I am using the following macro to identify invalid data entries in cells with data validation applied. Sub CheckOrder() ' Application.CommandBars("Formula Auditing").Visible = True ActiveSheet.CircleInvalid Sheets("Configuration").CircleInvalid Sheets("Parts_TakeOff").CircleInvalid MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) & " Configuration and Parts_TakeOff Sheets") End Sub Is there a way to count the number of invalid data entries (red circles) and write the number to a particular ce...