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

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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!

Register Yourself Login