Pivot Table Problem

bradleyjohnrob

New Member
Joined
Apr 16, 2015
Messages
1
I'm trying to get a 52 week sales/inventory report to auto fill from a data dump in the form of a pivot table. The only problem is that if there is no sales data from a week then that week does not show up in my pivot table because of the lack of source data. For example if there were no sales in week three and I made a pivot table grouped by week then there would only be three lines of data. I've unsuccessfully tried using a lookup function to fill the data in.

I've looked into the getpivotdata function, but I don't know if that will help me at all.

Is there anyone that has run into this problem and found a fix?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
welcome

One way is to create the pivot table from two tables - one your data dump & the other a list of all weeks. The relationship between the tables to give the dataset might be like

Code:
SELECT D.some_fields, W.week
FROM Weeks W LEFT OUTER JOIN DataDump D ON W.week = D.week

Here is one way to manually set it up. Give the data dump a defined named "DataDump". On another worksheet defined name "Weeks" - being a table with a header and then listed below the 52 weeks you want. Save the file. From a new file, ALT-D-P and choose the external data option at the first step: follow the wizard. Excel files, OK, choose the Excel data file, OK, see the data tables, choose the fields you want, OK. If you get a message about 'the wizard can't continue...' just OK & continue. When you see the MS Query GUI either drag & join between the tables and edit the link to make it an outer join, or, via the SQL button enter the SQL. SQL will be like above but to suit your exact set up. When OK, via the 'open door' icon exit MS Query and then finish the pivot table. The worksheet containing the pivot table can be moved into the source data file if you wish.

regards
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top