I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single workbooks.

I need help to extract data from these single workbooks to the overview using the hyperlinks automatically. Is this possible?

Please see photo below on what it looks like.

enter image description here


Extracting data from other worksheets is possible by using special formulas such as the one below:

='\\srv-file1.redacted\example share\[Time Sheet.xls]Sheet1'!$K$14

This formula has several parts:

  1. '\\srv-file1.redacted\example share\[Time Sheet.xls]Sheet1' is the reference to the sheet itself. This can be broken down into:

    1. \\srv-file1.redacted\example share\ is the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
    2. [Time Sheet.xls] is the file name of the workbook, wrapped in [].
    3. Sheet1 is the sheet within Time Sheet.xls that is being referenced.
  2. ! separates the filename/sheet from the specific cell being referenced.
  3. $K$14 is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such as K14.

Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)

GIF of file referencing

  • this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help! – fresh_french Dec 1 '18 at 13:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.