Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 3

This is Part 3 of a project where I attempt to create a macro to automatically convert a regular pivot table to formulas; similar to the way you can convert an OLAP pivot table to CUBE formulas. So far, I was able to create a macro that outputs Cell Referenced GETPIVOTDATA formulas for the target pivot table. My last objective is to see if I can bring the Page Fields along for the ride. That is to say, I would like to auto-create slicers to interactively alter the values returned by the GETPIVOTDATA formulas.

The screen capture below shows how Page Filters can be converted to Slicers that work with the auto-generated GETPIVOTDATA formulas.

ConvertPivovtToFormuasl2

You can try it for yourself a sample file.

Download Sample File

All the code for the macro is documented in the sample file, so I won’t bore you with the details. I will say that the tricky part of dynamically creating slicers is avoiding duplicates. Excel seems to freak out if you create a duplicate slicer. So each time the macro is run, we need to check for existing slicers. If existing slicers exists, we copy the slicer and point the copy to our pivot table. Otherwise we can create a new slicer with no issues.

By the way, I need to mention that I made heavy use of the excellent set of articles from Jan Karel Pieterse to get a head start on programming slicers. Check out Jan Karel’s site for all kinds of useful brain candy.

Well that’s it for this little side project of mine.

I would say that this macro has moderate usability. If you need a dashboard model fast, you can create a pivot table, run this macro, and voila! You’ve got yourself a set of formulas that can be moved, copied, and adjusted to create an interactive reporting model pretty quickly.

3 thoughts on “Convert Regular PivotTables to GETPIVOTDATA Formulas – PART 3

  1. Doug Glancy

    Pretty cool, Mike. It didn’t take that much code either.

    I’m curious, what’s the “column glitch” you address in your VBA?

    Also, have you ever seen the trick to allow you to refer to the Data header in a GetPivotData formula? So for example in cell D13 you could put:

    =IFERROR(GETPIVOTDATA(TEXT(D10,””),PivotSheet!$D$11,”Market”,PivotFormulas3!$B$11,”Quarters”,PivotFormulas3!$C$13),””)

    In order to refer to the header you have to “convert” it to text. I use the TEXT function here and Debra uses D10 & “” in her post at http://www.contextures.com: /xlPivot06.html#Reference (I was going to post about this when I first figured it out, but I was years too late). I’m not sure how it applies here unless you take this whole thing to another level of abstraction somehow.

  2. datapig Post author

    Doug: I didn’t know about the header referencing. Great Tip!

    As far as the glitch, it looks like in Excel 2007/2010/2013 the RowItems(1).LabelRange.Address property returns the wrong cell when there are no PivotFields in the Column area. Excel 2003 does not have this problem at all. So something changed in the object model between Excel 2003 and 2007.

    To see this glitch in action, open my sample file as is, make sure there are no fields in the Columns area, then go to the code and replace

    OffsetValue = -1
    Else: OffsetValue = 0

    WITH THIS

    OffsetValue = 0
    Else: OffsetValue = 0

    Walk through the code and you’ll see the RowItems(1).LabelRange.Address returning the cell address below the actual Label. I’m guessing this because Excel thinks the Values in the Column field means it should return the next cell down?

    I’m not smart enough to figure out why, but it looks like a bug to me. Maybe you can do a post on it 😉

Leave a Reply

Your email address will not be published. Required fields are marked *