Return Data from Privot table according to sums.

Copper Contributor

Hello Wizards, I need to get data form a pivot table according to the sum values in the table. I have a table that has each store's name, address, zip, etc on the vertical left of the table with the months across the top. The table measure sum of sales per product per store by month.

 

I need to find all stores that have sold within specific months. I have tried GETPIVOTDATA, but it seems to only be able to return the sum of sales of specific stores rather than the stores with specific sales. I also need to be able to adjust the month  of sales it is looking at.

 

Right now, I am using an ISBLANK function referencing the latest month and building a separate table from that. However, I can't adjust the dates with the current set up

 

7 Replies

@Max__ 

Hi

When you refer to a cell from a Pivot Table into another cell, Excel creates a GETPIVOTDATA function.

This function creates an ABSOLUTE reference. You can either:

  • Put the GetPivotData function in the edit mode F2 >> then edit the hardcoded reference, OR
  • Turn Off this functionality by clicking on File >> Options >> Formulas >> take the Check away from  "Use GetPivotData Function for Pivot Table References" >>OK

GetPivot.png

 

Hope that helps

Nabil Mourad

@nabilmourad 

 

Thank you so much for the suggestion, but I think the issue is more than i need to create a list of all the stores with sales through specific months and a list of of all the stores without sales for those specific months.

 

I understand how to edit the GETPIVOTDATA formula just fine but I'm not sure how to create a formula in a way that it will give all the store names without sales in a specific month. I would just reference the source data but there are only records of sales. rather than record for every month.

 

Does that make sense?

 

I've tried slicers but and filters but, again you can't sort by data that doesn't exist or is blank.

@Max__ 

would you please attach a sample Excel file?

@Max__ 

Your description says "show me every store that has not sold last month".

Your formula says "show me every store that has sold last month".

 

So, what should be?

 

@Detlef Lewin 

I need lists of both. one so that we know what stores are carrying what products still and one that says what stores have stopped selling.

@Max__ 

Here is a solution I made with Power Query.