Multiple Sheets Into One Pivot table (Not Pivot Wizard) + Database Question

Hi all! new to the forum and have some questions regarding a dashboard I'm trying to set up.

What I am trying to accomplish is a scatter plot using pivot charts so that I can specify the data down to the specific floor and unit (dealing with malls).

Easiest way off course is to have 1 raw database which I can do, but each month of the tenant data I use takes up 600 lines and my boss thinks that it will take up too much memory in the long run (true or not?) as there are 8 malls to consider.

What I am trying to do now is have a workbook for each mall with 1 year's worth of data on each sheet but I am not sure how or if I would be able to connect all these sheets an workbooks back to my dashboard scatter plot chart

So far, I have made a dynamic scatter plot with drop box options to change the data based on year, month, and floor based on a pivot chart but this is only for one mall. (Screens attached)

Please let me know what you think and if you have other suggestions to setting this up!



Hope someone answers me this time :o just looking for guidance