Retrieving data from several workbooks with dynamic name and index/match formula

  • Hi everybody,
    I have several Excel files saved in a folder. I need to retrieve information from these and save it in a masterfile called "Summary Total". The individual workbooks are all called "Results [Company Name]". I have a column in the workbook "Summary Total" from which I can get the company names.


    With my very limited VBA knowledge, I have two issues:
    1) How do I open a workbook based on the cell reference for company name. So that VBA opens the file of the company mentioned in the specific cell/workbook.
    2) Once I have opened the workbook, I need to retrieve information with the following Index/Match function. INDEX(B3:L44;MATCH("Umsatz";B3:B44;0);MATCH(YEAR('[Summary Total.xlsm]Company Financial Data'!$B$16);B3:L3;0))
    How can I convert this into VBA code, so it does the same thing as on the Excel sheet?


    I can provide the Excel files if that helps


    Best regards
    Philippe

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!