Ideas on collecting multiple workbooks

Showroom

New Member
Joined
Feb 13, 2017
Messages
13
Hi guys,

I have a small problem, and you would love some inputs on how to find a smart solution.

So I have multiple workbooks (10 plus), and each woorkbook has some data. Let us just call them datasets.
When I want to investigate something, create a report or create an Excel template I am using data from multiple datasets (workbooks).

Just so I can give you an example: One workbook can have prices, an other can have a forecast, yet an other can have a status on how many of each product we have on stock.
Now these datasets (workbooks) are being regularly updated. Let us assume, I want to make a graph or some kind of template, which include prices, forecast and stock. What we are doing now is, that we make vlookups on these datasets/workbooks which are located on a common driver.
This is working fine, but the problem is that my template become so heavy. It crashes multiple times. The reason is that my datasets have a lot of data, and I guess that vlookups are pretty heavy on Excel.

My question is: Do you have any ideas to make this more efficient?

My thought: I am thinking of creating a new workbook and gather all datasets in power query (Get data). Then I can connect all these datasets with a common product ID.
Do you know if I can make a lookup in a table which is in power query? and will this be less heavy? Otherwise I will need to create pivot tables in different sheets?

Please, give me some inputs :) All ideas are welcome :)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,147,632
Messages
5,742,230
Members
423,714
Latest member
ftp2jz

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