07-31-2019 09:55 AM
07-31-2019 09:55 AM
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
07-31-2019 12:35 PM
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:
Hope that helps
07-31-2019 12:46 PM - edited 07-31-2019 12:53 PM
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.
07-31-2019 10:13 PM
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?
08-01-2019 06:42 AM
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.