Pivot Table: Link back to / Modify raw data

[dave981]

Hi - I have question / request regarding Pivot Tables that I can't seem to solve:

Scenario: I have a worksheet full of ~360 tasks. Tasks are broken down into phases, have owners, and have status'. I leverage a pivot table to easily sort my data by owner, or by status. When reviewing the pivot table with others we want to update the raw data. This entails changing worksheets and scrolling to the task in question.

I'm looking for an easier way to update the raw / original data.

Options I've tried to pursue but have come up empty:
Option 1: Excel modification? Is there a setting in Excel 2007 that would allow me to change a value in my pivot table and have it propagate to the original data set (another Excel worksheet)? So far the answer seems to be "no". Is this possible?

Option 2: Hyperlink from Pivot Table to Raw data field.
- I cannot create hyperlinks within PivotTable data cells.
- I have used the "hyperlink()" function OUTSIDE of my pivot table, along w/ a "match()" on a key field in my pivot table to create hyperlinks back to my raw data... But this is prone to breaking.
- Is there a way to enable hyperlinks from within a Pivot Table? I have seen other requests from individuals that had a pivot table with actual hyperlinks (e.g. http://www.x.com or user@email.com) and they could not 'activate' them.

Option 3: Tool Tip VBA Form w/ Link?
- If I can't create hyperlinks directly in the table, then how about a function so then when i click (mouse over?) a pivot table cell a form appears with a hyperlink back to the source data?

Option 2 and 3 Assume that there's an way to get at the underlying location of the raw data (e.g. "Pivot Table Worksheet!E10" comes from "RawData!C4". I've dug through some VBA documentation at Microsoft.com but came up empty. <WRONG/Updated>I know this link must exist behind the scenes - In Excel 2007 when you mouse-over a pivot table cell Excel displays: "Row - Phase - Owner - Status", which is directly out of the raw data table/worksheet.</WRONG> [[This was an incorrect assumption... When i looked at the pivot table tool-tip closer I realize it was just walking through my data :'( row. (I guess if you had a really long list this would be helpful)]]

What is the VBA Code to revel the source cell? If i can get at that data, (and hyperlinking can be turned on w/in the pivot table...) then I can easily write a script to walk through the pivot table and create links back to the raw data... Not a perfect solution - but it takes away the need to search through the source data for the row I want.


Any thoughts / guidance on any of these options to solve my overall issue would be greatly appreciated.

Thanks!
Dave

*Updated Option 2/3 - corrected my assumption.

 

[Gerald Higgins]

Hi Dave.

I'm not aware of any way to do this.

Bear in mind that Pivot Tables are commonly used to summarise multiple records into single lines - it looks as if you are doing something like this.

So there's a fundamental problem here - if you want to change the results in the pivot table, which ONE or ONES of the MULTIPLE records would you change - just one ? All of them ? Some of them ?

Think of it this way.
1+1+1=3.
If you want the result to be 6, you can either
change all entries to 2, to get 2+2+2
change the first entry to 4, to get 4+1+1
change the second entry to 4, to get 1+4+1
and so on and on.
If Excel chose one of these solutions for you, it would very often choose a solution that you didn't really want.
The only real way to do this is for you to go back to the source data and change the records.

Have you considered Data Sort, and Data Filter, to help you group records together ?

 

[dave981]

Yeah, I leverage the pivot table mostly for grouping / sorting / ordering my data in the section on the left - the actual calculation region is (mostly) useless to me - other than to do a sub-total to show how many tasks were completed in a phase.

[ Phase ] | [Tasks] - [Status] [Unique ID] [Calculations.... ignored...]
Concept - Task 1 - Open - 1001 | 1
- Task 2 - Closed - 1345 | 1
Definition - Task 4 - Open - 2034 | 1
- ...

I currently also maintain a "unique Id" column for linking data to external applications (e.g. Visio Data Graphics).

If only there was a way to enable hyperlinks within an pivot table - Either by VBA or through some 'hidden feature' within Excel... Then i could force Excel to do what I want .

(This isn't the first time where i've pushed Excel in a direction it wasn't designed for...)

 

[kbenda]

I know this question was asked long ago, but maybe there are still some who are interested in an answer.

There is no built-in way in Excel to link back to the source data behind a pivot table, so you have to write a custom macro to do it. It was many years ago (2005?) when I wrote mine, so it's vague in my memory. As I recall, it was a long macro, but it was also one of my first attempts at programming, so it could probably be done more simply than I did it then. The way I did it, roughly, was to add an entry to the right-click/context menu that would call my macro. The macro itself started with the PivotCell property of the selected cell in the pivot table, and depending on whether it was a value, a subtotal, a row label, or whatever, it looped through all the row and column fields, counting the matches of the names, then went through the headers of the source data table (.SourceData property, I believe), looking for headers that matched the names found earlier. Process of elimination would eventually bring me to the cell I was looking for, or several cells in the the case of subtotals. If there were several cells separated by many rows so that they couldn't all fit on the screen, I hid intervening rows so all source cells were shown together.

I also made a simple companion macro (also on the right-click menu) that would bring the user back to the pivot table and refresh it. I believe it was the first macro that set the pivot table object as the input parameter to the second macro.

I wish I could remember more details or have the code handy and post it. But hopefully there's enough here to get people started on developing their own macros. Good luck!

 

[petiteappli]

Hi Dave,


I couldn't find this functionnality, so I wrote it.It is very tricky actually.
Anyway, I happen to have a team of marketeers available to test my addin, so I am pretty confident that when it is finished, we will have a robust writeback functionality for pivot tables, bundled with a few goodies that make it a breeze to use.
Let me know if you are still interested.

Kind Regards,
SB

 

[Jamarr123]

Hi Dave,


I couldn't find this functionnality, so I wrote it.It is very tricky actually.
Anyway, I happen to have a team of marketeers available to test my addin, so I am pretty confident that when it is finished, we will have a robust writeback functionality for pivot tables, bundled with a few goodies that make it a breeze to use.
Let me know if you are still interested.

Kind Regards,
SB

Hi SB,

I am still interested in this code if you have still got it.

Thanks,