Navigate Pivot Table Row Structure

Does anyone know how best to navigate a pivot table sideways (i.e. on same row but from column to column) using VBA?

If I pick any pivot item in the pivot table, I can find its parent item or column heading within the same column by simply doing

Please Login or Register  to view this content.

However, I've been pulling my hair out trying to find a function or method that will allow me to navigate sideways, i.e. return the corresponding value (or range object) at the same level from another column. See attached sample table for example. If I select any item under "Library Part", I want a function that will return the corresponding value or object (range or pivot item) under "Supplier".

PT.PNG

Please Login or Register  to view this content.

seems to be a natural candidate but I can't get it to work. GetPivotData doesn't seem to work for me either. I'm having a really bad day on this.


My pivot table is formatted to in tabular mode with column headings and collapsible rows. All I want to do is be able to pick any sub pivot item and get its left-most parent on the same row.

You might try vlookup to the left. You'll have to use cell locations as references instead of choosing the cells in the PT with your mouse. Not VBA, but might do the trick.

Pete

Thanks for the tip.

Regards.

Problem solved. Please see https://www.mrexcel.com/forum/excel-...structure.html.