best way to consolidate data from multiple workbooks question

Akira181

Board Regular
Joined
Mar 23, 2010
Messages
60
Office Version
  1. 365
Platform
  1. Windows
I have currently 14 workbooks that I want to pull a few rows of data from each and consolidate in a master file. Currently I'm just using formulas linked to the workbooks to automatically update and pull the rows that I want., e.g.: ='T:\Data\WIP\[Test File 1.xlsx]Sheet1'!H$21

I've set it up for two files and it's pulling the data and updating in the master file okay however, the final storage location for these files are still TBD so I haven't set it up for all 12 files yet. I'm slightly concerned that it may get a bit laggy when I do (roughly 28 rows x 110 columns of linked cells over two worksheets in the master file) , not to mention requiring updating if the file locations if they are ever moved.

I was wondering if the data consolidate tool will be a faster way of consolidating the data or is there no difference?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
840
Have you looked at Power Query? This an efficient way of collating data from Workbooks.
 

Akira181

Board Regular
Joined
Mar 23, 2010
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Stumbled across it just there in my googling but I haven't used it before. Will lag be an issue with accessing 14 files and updating so many cells or is that just unavoidable no matter the method?

I assume if the linked files are moved, there's no way to automatically update the links in excel too but with some quick messing about, it doesn't seem like too much effort to update the source locations so I can live with that.
 

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
840
It depends on where the files are stored. If they are accessed across a network then the refreshing will be longer than if stored on a drive. You can set up the data to refresh every time you open the master file or every so often. Yes if you move the origin files then yes the refresh will not work, but to change the location is quite simple.

Power Query can take data from tables, worksheets and a whol host of other sources.

Power query may look difficult but there are loads of videos on You Tube to help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,003
Members
430,103
Latest member
BIGGAZ

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