Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

excelpivottable
posted by: Cathy (rep: 53) on Tue Jun 19, 2018 at 4:41 pm
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
posted by: don (rep: 1989) on Wed Jun 20, 2018 at 5:05 am
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: 53) 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: 1989) 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: 53) 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