+ Reply to Thread
Results 1 to 5 of 5

Loop through table headings to get intersection value from pivot table

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    6

    Question Loop through table headings to get intersection value from pivot table

    I have a table with all possible row and column headings and a pivot table with just some of the same row and column heading as the table. I need a macro that would loop through the whole table to get the row and column headers intersect and pass the headings to the pivot table to find intersecting data to pass back to the table intersect. I have multiple named pivot tables that I need to get data from to populate the table.
    Example: the value 15.75 from the second column in pivot named “fullpvt” would go in the cell at the intersection of shop/holiday on the Table and the value 12.75 from the first column in pivot named “partpvt” would go in the cell at the intersection of shop/holiday on the Table

    My vba is self-taught but I have used the GetData to insert values in named ranges but my table has over 26 columns and 24 rows, that is too many named ranges.

    I have attached an example workbook to help illistrate what I need.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Loop through table headings to get intersection value from pivot table

    in b4:

    =IFERROR(GETPIVOTDATA("Hours",Pivot2!$A$3,"Reason",B$3,"Dept",$A4),0)+IFERROR(GETPIVOTDATA("Hours",Pivot1!$A$3,"Reason",B$3,"Dept",$A4),0)

    copy across and down
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Loop through table headings to get intersection value from pivot table

    This worked great the only problem is that I need to have the data that is put in the table stay the same and not change if someone edits the pivot table. If someone unchecks something in the pivot table the data in the table is gone. Other than doing a copy paste special values of the entire table to somewhere else, do you have any suggestions?
    Last edited by MOStans; 07-16-2013 at 01:44 PM.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Loop through table headings to get intersection value from pivot table

    in that case I would base your formulas on the source data and not the pivots

  5. #5
    Registered User
    Join Date
    07-16-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Loop through table headings to get intersection value from pivot table

    Thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table Headings
    By jomili in forum Excel General
    Replies: 3
    Last Post: 10-06-2010, 11:36 AM
  2. Problem with Pivot table headings
    By thedon_1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-13-2008, 06:43 PM
  3. Changing Pivot Table Headings
    By proline241 in forum Excel General
    Replies: 0
    Last Post: 12-12-2007, 04:00 PM
  4. [SOLVED] elucidating grouped headings in pivot table
    By Martin in forum Excel General
    Replies: 1
    Last Post: 06-13-2006, 08:15 AM
  5. pivot table row vs column headings
    By mrs.champ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1