Workbook link to current folder

abyerley Okay the example is attached.  I've tried to make this as dynamic as I can so if the filename or location of the MasterFile changes, the query will update accordingly.  The attached workbook is the one which consolidates row 10 from each of the files within the same folder: 

You can see more easily than the end product how this query works to consolidate data from every workbook in the same folder except the master file

And then, of course, the data transformation steps to merge the columns and eventually transpose them into columns as opposed to rows as shown above.   If additional files are added to the folder, they will automatically be transformed along with the others.  

I believe most of the query will work up until the "Inserted Text Between Delimiters" step.  These steps are very dependent on your naming convention of your files so it will need to be updated accordingly.  You might consider making a copy of the file (one for implementation and the other for reference).  Then hold Alt while opening the Excel application to start a new instance.  This should allow you to open the query editor in both documents & step through the query of the reference file, while making necessary adjustments to your implementation file steps highlighted below.  

Once configured correctly you can see the output is 42 columns comprised of merged data from the 21 files:

Hope this helps and good luck.