Pivot Table question

  • Hi,

    I am really far in the retreival of data from a pivot table, i am using the concatenate funtion together with getpivot data to get my data from the pivot, as shown below:

    =GETPIVOTDATA(FloorCareSalesAnalysis,CONCATENATE($I$38," ",$G$37," ",$D$6," ",$D$7," ",E38," "))

    where the different cells contain changeble values by means of comboboxes. I use a Named Range to refer to the pivot. However, i want to use also here the cell content of a particular cell, so i can determine which pivot table the formula has to look to. i cannot get it running with a normal cell reference like below, because then the formula expects the pivot table there directly, see below.
    =GETPIVOTDATA(E5,CONCATENATE($I$38," ",$G$37," ",$D$6," ",$D$7," ",D38," "))

    is there a way to make this also variable, in other words dependable on the content of cell e5 i refer to different pivot tables?

    I know this is a though one, but i hope you can solve.



  • So far i found a solution with the If formula nested in the above formulas, but this limits the possiblities. This one works,
    also included iserror.

    see below, any others?

    =IF(ISERROR((GETPIVOTDATA(IF(E5="GarmentCareSalesAnalysis",GarmentCareSalesAnalysis,FloorCareSalesAnalysis),CONCATENATE($I$38," ",$G$37," ",$D$6," ",$D$7," ",E38," ")))),"-",(GETPIVOTDATA(IF(E5="GarmentCareSalesAnalysis",GarmentCareSalesAnalysis,FloorCareSalesAnalysis),CONCATENATE($I$38," ",$G$37," ",$D$6," ",$D$7," ",E38," "))))

  • Hi Jack

    You can try the INDIRECT function, which returns a cell reference from a text value.


    where cell E5 holds the name of the Named Range.


  • Hi Fredrik, and forum!

    thanks a lot for your help!

    I wanted to show you the result.

    See enclosed a way how you can perfectly build interactive reporting on
    various pivot tables, and one of the solution to the fact excel cannot consolidate pivot tables that easily!!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!