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:
'\\srv-file1.redacted\example share\[Time Sheet.xls]Sheet1' is the reference to the sheet itself. This can be broken down into:
\\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.
[Time Sheet.xls] is the file name of the workbook, wrapped in
Sheet1 is the sheet within Time Sheet.xls that is being referenced.
! separates the filename/sheet from the specific cell being referenced.
$K$14 is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such as
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.)