In one of my excel files I would like to Reference cells in other excel file which is in the same folder. The obvious answer is to just put all of the other excel files into a different worksheet, I don't want to get into why, but that solution actually would end up being more work in the end.

I know how to reference another excel file C:documents/ .../blah.xls etc. The problem being that the folder that the link comes from is always moving. As the data is archived it moves from one network drive to another and the link breaks.

However, all the files stay in the same folder even though the folder moves locations.

So I was wondering if there was a way to Reference a file within the same folder but disregards its overall location.


For example)

I have a folder named 'August'. In the folder are four xls files; Master.xls , s1.xls, s2.xls and slave3.xls.

The master.xls is populated with certain values from s1 s2 and s3, but like I said, all of the actual files need to remain separate.

Creating the reference is easy enough but it doesn't last.
When August is over, the folder gets moved into an Archive and all of the References are broken since the C://Current/August/s1.xls moves to C://Archive/August/s1.xls.

But all 4 of the files always stay together so if there was a way to disregard where in the computer the files are being looked for and only referenced s1.xls in THIS folder then all references would never break.

So I was wondering if there was any reference formula or Macro code that would reference files in the same folder.

Thanks in advance