Help with Pivot tables that changes based on store

[BalloutMoe]

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

 

[Alex Blakenburg]

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]

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]

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.