Formula to get Relative Folder Reference to data in another file?
 

Hi,

I have a workbook that looks summarizes data that is in multiple (60) other
workbooks, and want to know if there is someway to craft my look such that
the folder structure doesn't manner --I want to be able to share this
workbook with a teammate and not have the lookups fail, so long as the
folder structure at the file level is the same.

Here is the setup:

Summary file
C:\xxx\xxx\xxx\summary file.xls
Data files
C:\xxx\xxx\xxx\data files\folder1\data.xls
C:\xxx\xxx\xxx\data files\folder2\data.xls
....
C:\xxx\xxx\xxx\data files\folder60\data.xls

I want to somehow craft my lookup -- right now my lookup is
='C:\xxx\xxx\xxx\data files\folder1\[data.xls]worksheet'!$P3 -- so that the
C:\xxx\xxx\xxx\ is added automatically. I tried using INFO("directory") to
get the path, but I could figure out how to use that in the formula.

TIA

--
Matthew