Need help with a Pivot Table thought

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
I am looking at creating a good amount of statistics from a block of data. For instance if I wanted to look at employee sales stats in a business that expands multiple locations, how could I go about looking looping through each location and creating a new table and chart for each location.

I have tried googling this, but I am not coming up with any ideas. I have an idea of creating a statement that will create an array of all the different locations. Then automate the creation of the pivot table using the cycling through the array as the Report Filter. Once the first pivot table is done, find the row the pivot table was in, go down two rows, and then place the next pivot table.

If this sounds like it would work please let me know. I will then work on the actual steps.

Thanks,

Jason
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

sdruley

Well-known Member
Joined
Oct 3, 2010
Messages
557
Office Version
  1. 365
Platform
  1. Windows
Jason,

My first thought was using a power-pivot but it appears that the scope of what you are doing probably fits into the scope of a normal pivot design. The secret behind successful pivot tables is the autonomous selection of column headings with the first row of data just underneath the headings. The pivot table is designed so you don't have to "loop through each location" or "create an array of different locations". It was named pivot because the columns of data can pivot around other columns and even rows of data to see interesting relationships. Once you understand the pivot you will realize that the approaches you mentioned in your post are not necessary. In conclusion instead of "working on the actual steps" you will be moving rows and columns around in the pivot to get the data you need.

PivotTable reports 101 - Excel - Office.com
offers an example that is relevant to what you are doing.

Steve
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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
Top