Link two workbooks

[rollingzep]

Hi,

I have a workbook to which I download files from external sources. This workbook also contains my report macros.
These data files are huge in volume and thus slows down my working reports.
I use the columns in my working reports to lookup fields in data files to populate certain fields.
The size of my workbook is approx 75K.

Is there a way to have split them into two workbooks, one which contains only data files and another workbook containing my working reports
and link them?
If so, how do i do that? Will this reduce the size of my reports workbook and make it faster?

Thanks

 

[jdellasala]

This is a job for Power Query AND the Data Model. It will take a few hours to figure out how to do what you need to do, but once you have it under your belt, it will take minutes - maybe seconds - to do what you need.
Mr. E has an excellent playlist on it here: Power Query
Also Mike from the ExcelIsFun YouTube channel has a complete course on it here: Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel (30+ Videos).
You should be able to automate bringing in new data which will be loaded to the Data Model which will generate a Pivot Table for your report - possibly with one click. This sounds too good to be true, but really, it is true. I was doing Inventory Management with a database of over 300K of Smartphones tracking about 50K in actual Inventory, and reconciling that with reports from four carriers. It would take me a day to do one carrier. Had I known Power Query, I could have done all 4 carriers in a couple of hours!
It will seem overwhelming at first, but stick with it - it's well worth the time! And you can always come back here if you run into a speed bump! Good luck.