+ Reply to Thread
Results 1 to 2 of 2

GetPivotData drag for entire column

  1. #1
    Registered User
    Join Date
    10-24-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    15

    GetPivotData drag for entire column

    Helly everyone
    I have one sheet with the pivot table, containing several row dimensions and one column dimension, which i have extracted from a cube. My problem is that i want to copy the data to "Load Demo", but when i try to use the getpivotdata and copy it into the Load sheet i have to manually enter each cell and click on the specific cell in the Data Demo sheet, which is really cumbersome work. Is there no way to drag the entire columns? I really don't know where to start since the formula that Excel generate for the Getpivotdata sounds like:
    =GETPIVOTDATA("[Measures].[Value Local]";'Data Demo'!$A$6;"[Account].[Account ID]";"[Account].[Account ID].&[100100]";"[Customer].[Customer ID]";"[Customer].[Customer ID].&[WSA]";"[Product].[Product ID]";"[Product].[Product ID].&[PA]";"[Calendar].[Y -Q - M]";"[Calendar].[Y -Q - M].[Calendar Month].&[201402]";"[Account].[Account external]";"[Account].[Account external].[Account ID Account External Niv7].&[100100]";"[Customer].[Customer]";"[Customer].[Customer].[Customer ID Customer Niv3].&[WSA]";"[Product].[Product]";"[Product].[Product].[Product ID Product Niv3].&[PA]")

    Please check the excel file

    Hope someone can help
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: GetPivotData drag for entire column

    Hi,

    You should be able to concatenate the relevant information in using the hidden cells like this
    =IFERROR(GETPIVOTDATA("[Measures].[Value Local]",'Data Demo'!$A$6,"[Account].[Account ID]","[Account].[Account ID].&["&$D17&"]","[Customer].[Customer ID]","[Customer].[Customer ID].&["&$C17&"]","[Product].[Product ID]","[Product].[Product ID].&["&$E17&"]","[Calendar].[Y -Q - M]","[Calendar].[Y -Q - M].[Calendar Month].&["&TEXT(K$16,"yyyymm")&"]","[Account].[Account external]","[Account].[Account external].[Account ID Account External Niv7].&["&$D17&"]","[Customer].[Customer]","[Customer].[Customer].[Customer ID Customer Niv3].&["&$C17&"]","[Product].[Product]","[Product].[Product].[Product ID Product Niv3].&["&$E17&"]"),0)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Replies: 12
    Last Post: 09-16-2015, 12:35 PM
  2. [SOLVED] GETPIVOTDATA – How to force getpivotdata to accept missing data
    By scottc_00 in forum Excel Formulas & Functions
    Replies: 39
    Last Post: 02-11-2015, 03:23 PM
  3. [SOLVED] Drag formula issue with GETPIVOTDATA function.
    By Stew1234 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-09-2014, 05:28 AM
  4. Replies: 1
    Last Post: 04-05-2013, 12:28 PM
  5. Replies: 3
    Last Post: 07-28-2012, 09:58 PM
  6. [SOLVED] Excel 2007 : Drag Formula Down Entire Column
    By KSSLR in forum Excel General
    Replies: 2
    Last Post: 06-18-2012, 12:57 PM
  7. Drag and Drop entire row but Columns dont change?
    By ThaGonz in forum Excel General
    Replies: 1
    Last Post: 09-07-2011, 07:14 PM

Tags for this Thread

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