Excel 2010 - pivot table How to create Relationship?

0

In Excel 2013, in the ribbon: PivotTable Tools - Analyze - Calucations, there's a button called "Relationships" that you can use to link pivot tables that have different source data.  I don't see it in Excel 2010 (which is what my work is using) in the PivotTable Tools - Options ribbon.

Does Excel 2010 have an equivalent? 

If there isn't, what's the best way to automatically update a field of a pivot table when the field is updated in another table? (The source data of the tables have common fields but not everything is the same so the source data themselves cannot be combined and use a single source to create the 2 pivot tables and in turn be connected using slicer.)

Thanks.

Answer
Discuss

Answers

0

Sadly, the Relationships feature for PivotTables is not available in Excel 2010 or earlier and is only there for some versions of Excel 2013.

It looks like you will need to do one of the following things to get the same functionality:

  • Use INDEX/MACTH or VLOOKUP to combine data from multiple tables and then get your PivotTables running off of the new data.
  • Download PowerPivot for Excel - it's a free add-in from Microsoft. The link I provided is the download link for the version that works for Excel 2010 and 2013.
  • Beg them to upgrade to a newer version :P
Discuss

Discussion

Darn, I was afriad of that. 

My data is actually coming in from Access and unfortunately I can't combine them. The sources have some common information fields but not everything. If I try to combine them, it results in certain value fields not calculating correctly.

I only know very basic macro, but I wonder if there's something that can be written to do,
"Everytime PivotTable1 Field A is change to X, then set PivotTable2 Field A to X; Everytime PivotTable1 Field B is change to Y and Z, then set PivotTable2 Field B to Y and Z" ????
Cathy (rep: 2) Jun 20, '18 at 9:48 am
For sure it could be done with a macro. That said, I'd try the PowerPivot add-in first and see how that goes. If that doesn't work go the macro route. You can for sure ask questions about how to make the macro on this forum, but you should ask specific questions. If you want a macro built for you from scratch, use the Contact link at the top of the page and you can pay to have it made for you. Or, try option 3 from the answer, if it's mission-critical, maybe they can update just your workstation.
don (rep: 1482) Jun 20, '18 at 2:16 pm
A colleague has mentioned PowerPivot in previous conversations but I must admit I haven't heard of it until recently and it's not installed on my work excel by default.

Now that I've watched a couple of videos on it, it might just be the answer to get me pass the last hurdle. Definitely will need to play around and familiarize myself  with it and see what wonders it can do. Thanks for the advice. Cheers.
Cathy (rep: 2) Jun 20, '18 at 8:43 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login