Share via

Dynamic GETPIVOTDATA query in Power Pivot

Anonymous
2021-12-21T19:05:40+00:00

Hi,

Is it possible to make the data field dynamic in a "GETPIVOTDATA" formula when using power pivot?

My formula is below, I have made the some of the items dynamic with no issue, but I want to make the item "sum of 71.Heat & Light" dynamic too, as its a reference to our GL codes and i'll be repeating the formula for each one.

I have tried the formula: &["& Cell ref &"]" but it returns a #ref error. With a regular pivot table i think it can be achieved using "concenate function", but i can't get this to work either.

=GETPIVOTDATA("[Measures].[Sum of 71.Heat & Light]",'Flash Act'!$A$1,"[Date LkUp].[PdWk]","[Date LkUp].[PdWk].&["&$BX8&"]","[Site LkUp].[Code]","[Site LkUp].[Code].&["&BY$7&"]")

Any advice is greatly appreciated, pretty new to PP so may well be missing something obvious. Cheers!

Microsoft 365 and Office | Excel | For business | Android

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2021-12-22T03:03:01+00:00

    Hi,

    If it is PowerPivot that you are dealing with, then you should be using CUBE functions. If you can share a file with a simple example and show the problem you are facing there, i can try to help.

    Was this answer helpful?

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-12-24T06:48:44+00:00

    Hi sumwatson,

    If you still need further support, please do not hesitate to reply to me.

    Best regards,

    Jazlyn

    Was this answer helpful?

    0 comments