Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Getpivotdata reference question

    All,
    The following Getpivotdata formula works...

    GETPIVOTDATA("[Measures].["&$C10&"]",'CSSC-R'!$C$11,"[Qual Walk].[Qual Walk]","[Qual Walk].[Qual Walk].[Categories].&["&$D10&"]","[North Central Service Region].[Service Region NorthCentral]","[North Central Service Region].[Service Region NorthCentral].[Area].&["&$F10&"]","[Call Type].[Call Type]","[Call Type].[Call Type].[Call Type].&["&LEFT($H10,1)&"]","[Call Date].[Call Date]","[Call Date].[Call Date].[Call Month].&["&O$1&"]&["&O$2&"]","[Customer Type].[Customer Type]","[Customer Type].[Customer Type].[Customer Type].&["&$I10&"]")

    'CSSC-R'!$C$11 is the location of the pivot table I am referencing. I need to use the value in a cell as the sheet name for the formula. In my mind, It should look like this:

    '"& $B10 &"'!$C$11 which would make the entire formula look like this:

    GETPIVOTDATA("[Measures].["&$C10&"]",'"& $B10 &"'!$C$11,"[Qual Walk].[Qual Walk]","[Qual Walk].[Qual Walk].[Categories].&["&$D10&"]","[North Central Service Region].[Service Region NorthCentral]","[North Central Service Region].[Service Region NorthCentral].[Area].&["&$F10&"]","[Call Type].[Call Type]","[Call Type].[Call Type].[Call Type].&["&LEFT($H10,1)&"]","[Call Date].[Call Date]","[Call Date].[Call Date].[Call Month].&["&O$1&"]&["&O$2&"]","[Customer Type].[Customer Type]","[Customer Type].[Customer Type].[Customer Type].&["&$I10&"]")

    The formula returns a #REF error.

    Any thoughts?

  2. #2
    Join Date
    Jun 2007
    Posts
    74
    OK, answered my own question. Have to use the Indirect function for the address:

    =GETPIVOTDATA("[Measures].[FiOS Video Qualified Calls]", INDIRECT("'" & B10 & "'!" & C10),"[Qual Walk].[Qual Walk]","[Qual Walk].[Qual Walk].[Categories].&[HSI Migrator]","[North Central Service Region].[Service Region NorthCentral]","[North Central Service Region].[Service Region NorthCentral].[Area].&[California]","[Call Type].[Call Type]","[Call Type].[Call Type].[Call Type].&[B]","[Call Date].[Call Date]","[Call Date].[Call Date].[Call Month].&[2011]&[1]","[Customer Type].[Customer Type]","[Customer Type].[Customer Type].[Customer Type].&[E]")

Posting Permissions

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