Pivot Table Data Source

Apr 10, 2013

A co-worker of mine set up a pivot table using data saved on an external data source on a shared drive. I would like to create a similar pivot table myself, linked to his pivot table so when he gets updated data, my pivot table also will update accordingly. My question has to do with the pivot table data source. How do I copy the connection?

View 3 Replies


ADVERTISEMENT

Pivot Table Retains Old Source Data In Addition To New Source Data

Sep 7, 2006

I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

Does anyone know how to purge the old data from the internal Pivot Table memory?

I hope this is enough information....let me know if you need more.

Thanks in advance for any help,

Jon

View 9 Replies View Related

Linking Pivot Table To Data Source Table?

Jul 14, 2014

I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?

View 2 Replies View Related

Pivot Table Source Data

Aug 24, 2009

I was wondering if anyone knows how to change the souce data for a Pivot Table?
Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.

View 3 Replies View Related

Pull The Data From, Hence The Source Of The Pivot Table

Mar 27, 2009

I have a pivot table created. How do I determine which sheet it is pulling the data from, hence the source of the pivot table? Also, if can you go into source and modify data points, then in pivot table, will the results would be automatically modified with updated data points?

View 2 Replies View Related

Find Out The Source Data From A Pivot Table?

May 10, 2009

I've got a bit of a problem revealing pivot table source data, so just wondering if anyone know how to reveal it? My colleague sent me the spreadsheet, I can see the pivot table, but I can't find the source data that the pivot table links to.

View 3 Replies View Related

Pivot Table - Dynamic Source Data

Sep 26, 2011

I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.

These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?

View 5 Replies View Related

Pivot Table Data Source After Email

Feb 22, 2010

why the data reference for a pivot table is changing after I email the workbook to a client. The source data and the pivot tables are all part of the same workbook and I can't understand why its saying can't find source data. Excel 2007 is being used on both computers.

View 9 Replies View Related

Pivot Table Field Not In Data Source

Apr 24, 2006

I am using pivot tables which were created by another (no longer contactable) and have found two fields in the field list which are not part of the source data i.e. neither are column headings but are used in the pivot table.

My questions are:

1. How are these field created?

2. How can I examine these fields for formulae (as I have found one of them returns an incorrect value)?

Field1 return a numerical value which is the difference between two columns
Field2 shows this as a % (but incorrectly)

View 4 Replies View Related

Reformat Data For Pivot Table Source

May 31, 2007

My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset.

Spreadsheet 1
....Description............................Dept...................January acquisition
.....Building____________________Plant______________1,000,000

Spreadsheet 2
.....Description..........................Dept...................February acquisition
......Building___________________Plant______________1,000,000
.....Welder____________________Metal_________________5,000

What I want is this

Spreadsheet 3
.....Description.........................Dept..................January Acq..........February Acq
......Building__________________Plant____________1,000,000__________1,000,000
......Welder___________________Metal_________________________________5,000

View 4 Replies View Related

Maintain Source Data Formatting In Pivot Table?

Nov 20, 2010

I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.

I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.

View 2 Replies View Related

Update The Pivot Table Data Source From Cell?

Aug 8, 2013

I have a pivot table whose data source is located on a shared network drive. Each day a new data source is uploaded and the only thing that changes is the date in the filename (ie. DataSource080813.xlsx). I am trying to find a way to set the data source to update with todays date automatically each day so that when you open the workbook it is always current days data. I have a cell (B2) with a formula in it that gives me the file location of todays file (ie. X:TeamFolderFile1DataSource080813.xlsx, where the date is derived using the TODAY() function. Is there a way to set the pivot table data source to cell B2 and have it use the text from B2 as the file location?

View 1 Replies View Related

Can't Refresh Pivot Table When Using External Data Source

Apr 30, 2014

I am using an Excel file (Raw.xlsx) to store my raw data, and take another Excel file (Pivot.xlsx) to make PivotTable from Raw.xlsx

When generating the PivotTable, every works fine. When click [Refresh] in the Raw.xlsx, whatever changes been made in the Raw.xlsx can "sync" to the Pivot.xlsx

However, once I save and reopen the Pivot.xlsx, I can't refresh the PivotTable anymore. I've tried changing Data Source, but it doesn't work.

View 1 Replies View Related

Refreshing Actual Data Source Of Pivot Table

Dec 31, 2013

Trying to create a basic pivot table from a CSV file for our users. What I've done in the past is open my CSV and then take the option Insert > Pivot Table to create the pivot table from the open CSV/worksheet. My question here is .... how can I save this .XLS and refresh the CSV data within? Or do I need to use an external data source? I don't want to have to use any drivers. I'd love to be able to just point to a CSV or TXT file on our server and that be that.

For something else I have used Web Query strings to point to .php scripts that send back data in XML format. Is this an option?

Again, the goal is for me to create a pivot table and just have our user's open it up and have current/refreshed data within. We can run jobs overnight or on-demand that update the data source.

View 1 Replies View Related

Update The Source Data For The Pivot Table In Database

Jan 7, 2009

I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:

View 7 Replies View Related

Hidden Columns In Pivot Table Source Data

Jul 17, 2009

I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.

View 2 Replies View Related

Determine The Source Data That Was Used To Create The Pivot Table?

Sep 7, 2009

Given a pivot table, is there any way that I can determine the source data that was used to create the pivot table?

I suspect that the pivot table was created using only part of the data, but I'm not sure.

View 9 Replies View Related

Pivot Table - Break Connection To Data Source?

Apr 14, 2011

if it's possible to break the connection between a pivot table and the data source whilst still maintaining the data in the table? I could try copy/paste special/paste values but thought there migth be a 'proper' way to preserve the data.

View 8 Replies View Related

Pivot Table - Edit What Parts Of Data Source Appear?

May 22, 2014

I noticed that when I click on a cell in my Pivot Table a new worksheet pops up with the requested information (essentially a filtered version of my data source depending on what cell I clicked) is there a way for that information to appear on the same worksheet as my Pivot Table? And if so can I also edit what parts of the data source appear?

View 1 Replies View Related

Extracting Source Data From A 'text-only' Pivot Table

Nov 29, 2007

I work in my Client's office and assist in settling construction disputes. Part of this work is to browse/search their server for documents that may assist in strengthening their case.

During this review I have found an excel document which is a text-only version of a pivot table, ie someone has done a copy, paste special, values into this sheet. I need to extract the original source data from this table back into the list format, as the original source of the data cannot be located

The row titles on the left are activity descriptions, the column headers are dates and the data in the body of the table is hours. As an idea of size the data is spread over 213 columns and 45 rows. Their are more blank cells in the table than entries.

What I would like to do is create the data in it's original form ie

Column A; Date
Column B; Activity Description
Column C; Hours

and have a separate row for each instance of an entry of hours from the pivot table.

View 9 Replies View Related

Refine Pivot Table Source Data On Drill Down

Oct 26, 2007

Attached is a file which shows the problem. I bascially have two headings. The first heading contains the letter "b" 9 times but only has values against two "b"s. When this is entered in a pivot table and the value is drilled down (in this case 3, highlighted in yellow), I only want the numbers attached to the two "b"s to be shown rather than all the 9 b's (thereby omitted all the zeros).

View 3 Replies View Related

Automatic Refresh Of Pivot Table On Changes In External Data Source?

Dec 4, 2013

I have a pivot table report connected to an external data source (OLAP cube).I want to automatically refresh the pivot table report everytime the OLAP cube data changes.

I know we can use Automatic refresh on open of workbook or time intervals after which to refresh .But i want the auto refresh to work even if the workbook is already open and there is no definite time interval after which the cube is likely to change.SO,by defining intervals for auto refresh i do not intend to waste time refreshing even if there is no change.

There are multiple pivot table reports and pivot charts connected to the same OLAP cube.

View 1 Replies View Related

Multiple Pivot Tables - Single Table Of Source Data

Mar 9, 2014

I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.

Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...

Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg

View 1 Replies View Related

Excel 2010 :: How To View Source Data Of Pivot Table

Jan 6, 2012

I'm used to work with the Excel 2003 edition, however I just changed the version to 2010. As result of that I do have the following question:

How can you view the source data of a pivot table in Excel 2010?

View 6 Replies View Related

Copy Pivot Table Format And Create New Data Source?

Apr 26, 2012

I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.

View 5 Replies View Related

Pivot Table :: The Query And External Data Source Is Disabled

Feb 5, 2007

In pivot table field Setting > Advanced > page field option, the selection for "the query and external data source is disabled. How do i enable it.

View 9 Replies View Related

Excel 2007 :: Crashes When Trying To Update Data Source In Pivot Table

Sep 17, 2010

Excel 2007 crashing when trying to up date a data source within a pivot table?

View 3 Replies View Related

Excel 2007 :: Find Source Data Range Of Pivot Table?

Feb 4, 2013

how to find the source data range of a pivot table (that already exists) in Excel 2007?

View 1 Replies View Related

Create Pivot Table: Cannot Open Pivot Table Source File

Jan 4, 2010

I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

View 14 Replies View Related

VBA - Change Data Source Of Pivot Table With Range Changing Based On Cell Value?

Mar 5, 2013

I have the following code to update a pivot table:

Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)

Next pt

the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved