title: Making a masterworkbook with a table pulling data from child
body: <p>In my directory c:/test I have multiple workbooks named "xx-xxxxx.xlsm" where "xx-xxxxx" is the registrationnumber of the car that is documented in the specific file.</p>
  
  <p>In all the xx-xxxxx.xlsm files there is a sheet named "Summary", and in this sheet, the data structure is the same in all the workbooks - b1 is the reg.nr., b2 is cartype, b3 is the purchase date, b4 is the user etc.</p>
  
  <p>Now I want to make a masterworkbook with a masterTABLE sheet that sums up all the summarysheets in one table without opening the individual workbooks. 
  In this table I have the reg.no in column A, cartype in column B, purchasedate in column C, user in column D and so forth.</p>
  
  <p>The obvious solution is to make a formula saying ='C:/test/[xx-xxxxx.xlsm]Table'!$b$2, and then manually insert the appropriate file name. But since I have the reg.no. in the first column in the master table and the same reg.no in the file name (and MANY cars/files/rows/columns), I would like to have a formula like ='C:/test/[b2]Table'!$b$2 or VLOOKUP and just copy it down each column/row. But this of course does not work.</p>
  
  <p>I have tried other solutions on similar challenges suggested by other users in this and other forums , but they do not work for me (i.e. I am not capable enough in VBA to modify the macros suggested to my needs.</p>
  
  <p>What I want is in the masterTable-sheet in the masterworkbook to have all the registration numbers of the cars in the first column and one record/car pr row, and based on this number, I want formulas/macros in column B that gets the cartype from A3 in all the separate workbooks, in column C I want a formula that gets the purchase date from A4 from all the workbooks.</p>
  
  <p>Further I would, in the masterworkbook, like to have a SETUP sheet where I write the path to the directory i.e. c:/test so that it can be used in the formula/macro that is the solution on the above problem. Or if the path can be derived automatically from the directory in which the active masterworkbook is saved.</p>
  
  <p>Can anyone out there help me?</p>
  
  <p>And please note that I am a novice in VBA and need it explained loud and clear ;)</p>
  
  <p>Thank you in advance :)</p>