Pivot Table Formula Dependant On List Selection

  • Im trying to create a 'drill-down' interface with the GETPIVOTDATA command.


    I believe (but im not sure) that this will require several different formulas.


    e.g., assuming this formula resides in A1, this returns all data in the pivot $A$6 for Monday of 6/1/2009:


    Quote

    =GETPIVOTDATA("Sum of Mon",$A$6,"Week",DATE(2009,6,1))


    however for cell A1, if the user wants to drill down, then the required formula expands to the following..in this case we are drilling down to Name=Baby Becket/Ball, Stage=Infant..and so on..


    Quote

    =GETPIVOTDATA("Sum of Mon",$A$6,"Name","Baby Becket/Ball","Week",DATE(2009,6,1),"Stage","Infant","B/L","B","WL",)


    Essentially, without writing a bunch of IF's in the formula for A1...is there a way to put these formulas in a lookup table, and then depending on what the user chooses on how they want to analyze the data (e.g., they may select Name, Stage, etc from a drop down list elsewhere on the sheet), the appropriate formula is populate in A1?


    In a nutshell: Can the formula of a cell be changed depending on what the selection value is of another cell or list value?


    This could probably been done easily via VBA, but if there is a formula or vlookup based solution that would be easier..

  • Re: Cell Formula Dependant On List Selection


    Does the user not have the ability to drill down within the Pivot Table itself?


    Seems like you are trying to recreate pivot table functionality without using a pivot table... which sounds kinda daft... or maybe i have misunderstood the question ;)

  • Re: Cell Formula Dependant On List Selection


    Quote

    Does the user not have the ability to drill down within the Pivot Table itself?


    Im also trying to use the pivot table directly, however the answer to my posted question in the general sense would be useful to know, regardless..

  • Re: Cell Formula Dependant On List Selection


    Quote

    Does the user not have the ability to drill down within the Pivot Table itself?

    Is that a yes or no?


    You can certainly use list selections in formulae, just replace any static formula arguments to a cell housing the list choice. BUT, if it's cell address or name, you will need the INDIRECT function.

  • Re: Cell Formula Dependant On List Selection


    Quote

    Will Riley: Does the user not have the ability to drill down within the Pivot Table itself?


    No, the typical user will not have this ability, so the intent is to build a user friendly interface that will interact with the pivot table parameters.


    Quote

    Dave Hawley: You can certainly use list selections in formulae, just replace any static formula arguments to a cell housing the list choice. BUT, if it's cell address or name, you will need the INDIRECT function.


    Dr. Hawley... to confirm if your answer is appropriate..(or perhaps to confirm I worded my objective correctly), Im going to restate my objective more succinctly:


    - Can something equivalent to the following be accomplished:


    IF the value of Cell A1="1", then:
    =GETPIVOTDATA("Sum of Mon",$A$6,"Week", DATE(2009,6,1))


    If the value of Cell A2="2" then


    =GETPIVOTDATA("Sum of Mon",$A$6,"Name","Baby Becket/Ball","Week",DATE(2009,6,1),"Stage","Infant","B/L","B","WL",)


    and so on...


    Does your suggestion still apply to this clarified objective?

  • Re: Cell Formula Dependant On List Selection


    Why not allow them to Drill Down if your aim is "user friendly"?


    Quote

    Does your suggestion still apply to this clarified objective?

    It might be clarified in your mind, but I have no idea what A1 and A2 have to do with anything. However, why not confirm my answer yourself???

  • Re: Cell Formula Dependant On List Selection


    Quote

    It might be clarified in your mind, but I have no idea what A1 and A2 have to do with anything. However, why not confirm my answer yourself???


    Ive edited the string above to clarify i was referring to Cell A1, etc..


    The string above in which i present a clarification was intended to keep within your guidelines of this site, to ensure the post is clear to a wide audience. After reading my original post, I thought it was not worded that well, and determined there may be a simpler way to present my question, thus helping the next person reading the post.


    Ill take a look at your suggested use of a List and INDIRECT...and will post findings accordingly...

  • Re: Pivot Table Formula Dependant On List Selection


    I cant tell if it would help in the original context, but the CHOOSE function allows you to have different formulas depending on a numbered index - it might be helpful to you


    =CHOOSE(2,1+3,6-4,9) would evaluate the second formula, i.e. 6-4=2.

    "I'm sorry, the number you have reached is an imaginary number. Please rotate your phone 90 degrees and dial again."