dynamic selection criteria for pivot sum of # in excel 2010

[petit_Fleur]

Hello Comunity,

my problem is a bit complex. So I hope not to lose anyone.

I would need to create a spreadsheet in which managment can select certain criteria in a seperate sheet than the pivot is. one of the selection criteria would be region hierarchies. So they can select Austria as country or Alpine (Swiss, Austria and Lichtenstein) as Hub or Central Europe (Alpine + Germany + ceveral other) as Region. Each country returns ceveral criteria itself (so let's say acutals of this year, prior year and plan and this for ceveral products). I managed that by setting a get pivot formula and linking this formula to a dynamic search linked to the drop down list on the managers spread sheet.

My Problem: The information is downloaded from BW, which only returnd a list of countries without this hierarchical selection. That means I need three collumns to determin this information (one each for country, hub and region). Subsequently I have 3 seperate collumns in my pivot. So I set the pivot up accordingly; first returning values for Region, than split into hubs and hubs split into countries. The data shown to managment is selected via a getpivot formula which itself is dynamically linked to a drop down list on the managment sheet. Linking the selection drop down box with the pivot managment is able to dynamicly select the country, but in order for the pivot to return the HUB it would need calculating sums by time (actuals per year or plan) and by product. The Pivot does that as "Alpine sum of Actual 2013".

Here my problems start: This sum cannot be select in a getpivotformula. And even if I could how could I integrate it with the other getpivotformula for the country?

I would be very thankful for your advice.

[minnesotaart]

What about using multiple pivots and pointing to the correct one depending on the selection criteria?

[petit_Fleur]

Hey!

I was thinking about that as well, but that would make the formula very very long. I have 8 Regions. How could I manage such a formula? Plus I have this issue with countries as well as products.

Thanks.

[minnesotaart]

Whatever you are using for "selectors" (buttons, dropdowns, etc) will come up with a value that is stored in a cell somewhere. When all selections are made, you have a unique set of "criteria" that determine what you are going to look for. For example, it seems the main criteria you are looking for is Region, Hub or Country. This info can be stored in a cell. Then you are looking for a value from one of those (a certain Region, a certain Hub or a certain Country). That information can be stored in another cell. And so on down the criteria list. When you are done, you have all the criteria to figure out what cell of the pivot you need to go get, so write your GETPIVOTDATA formula referencing the criteria cells.

[petit_Fleur]

Hi again!

that's exactly what I did. So teh get Pivot selects data as GETPIVOTDATA(" Actual 2013";$B$12;"Product Line";$A$2(my selection cell);"Country";$A$3(my selection cell);"Month";H$1;"HUB";$A$1(my selection cell);"Region";$A$5(my selection cell);"Product Type";$A$4).
Therefore I have 3 cells (one for country - A3, another for Hub -A1 - and a last for Region - A5). The problem occures when I select a hub, ie Alpine. Then my getpivot tries selecting the hub as a country in its formula. Any ideas how I can get around this issue?

thanks.

[minnesotaart]

You are hard coding the rows/columns in your formula ("Actual 2013", "Product Line", etc.). Make them variable too. Put the correct text in a cell depending on what combination of criteria was chosen. In essence, you are building a text string that you want to include in your GETPIVOTDATA formula. Since "Hub" is a higher level in your pivot vs. country, you can use an IF statement to include/exclude that item in your formula. Just break it down into all of it's components and you'll see how to put it back together.
GETPIVOTDATA($C$1,$B$12,$C$2,$A$2,$C$3,$A$3,if($A$3="Hub",&$C$4","&$A$4,""),....and so on

[petit_Fleur]

Those cells are dynamic. Cells A1 to A5 are the country, hub, etc. So these are all dynamic. I tried setting up if formulas. The problem with the if formula is too that the formula get s to long and complex. Who is supposed to deal with that spreadsheet later if errors occure?

[minnesotaart]

Post a sample spreadsheet so I can look at it.

[petit_Fleur]

Hey!

It doesn't work. Maybe the file is too large. I need finding a different way.

cheers.

[oeldere]

If you post an (small) example of your file (wihtout confidential information), forummembers could take a look at it.

[petit_Fleur]

I managed it differently now. I created four pivots based on the same source. One each for Country, hub, region and total. I than linked them to a variable field using getpivot and in a seperate field added all four data sets together. The point is, that getpivot will return 0 when it cannot find a figure (such as for a country if there is only a hub region selected). the the variable fields had themselves a formula which said:if hub=region write blank, otherwise vlookup region to hub. In the end I got only one getpivot data set to return me values, the others returned 0. The sum was than what I neededn.

maybe that idea helps other as well.

Cheers and many thanks for the help provided.