Help with Pivot tables that changes based on store

BalloutMoe

Board Regular
Joined
Jun 4, 2021
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I am looking for some help to guide me in the right direction. I have 10 store locations that all have a BANK file in their folder. Inside the bank files for each location their is a table called INFO that has
DATE | DESCRIPTION | AMOUNT | TYPE

I would like to make a pivot table that has a drop down list menu with each shop name. When selected the data will change accordingly. Kind of like filtering through a year or month but with location and if possible to be able to compare the shops.

Do I need to make a pivot table for each location first then combine them or can I just use the data. I am kind of stuck as I am very new to pivot table. or what is the best way to go about doing something like this.

Thank you
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,026
Office Version
  1. 365
Platform
  1. Windows
It sounds like you have the data in separate workbooks and separate folders.
Pivot tables work best when all the data in in the one worksheet.

If all the Bank files were in the one folder then it would be relatively easy to use Power Query to pull in all the data into one sheet and use that for you pivot table.
Automation works best when the data is in a consistent location with a consistent name and I don't know if that is the case for you.
 

BalloutMoe

Board Regular
Joined
Jun 4, 2021
Messages
60
Office Version
  1. 365
Platform
  1. Windows
It sounds like you have the data in separate workbooks and separate folders.
Pivot tables work best when all the data in in the one worksheet.

If all the Bank files were in the one folder then it would be relatively easy to use Power Query to pull in all the data into one sheet and use that for you pivot table.
Automation works best when the data is in a consistent location with a consistent name and I don't know if that is the case for you.
I can have them put into one worksheet but different ranges as they all need to be separate if that is possible
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,026
Office Version
  1. 365
Platform
  1. Windows
It is more an issue of workflow. The pivot itself really needs the data to be in one table. This can be achieved with Power Query.
How do you get the bank files ?
Do they come to you in one folder as a text / csv file / Excel ? (Power Query > Get Data > From Folder).
Why do you still need the data to be stored in separate workbooks or tables ? (Again you can use Power Query to Merge the data and send it to a pivot table)

In theory you can use a Pivot to consolidate worksheets but you lose quite a bit off functionality.
 

Forum statistics

Threads
1,143,840
Messages
5,721,100
Members
422,339
Latest member
SHIVATVM

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