Need Help Constructing External Reference With Variables

[tsmedley]

Hi There,

I have a sheet which I use to collate store information sent to me directly on a weekly basis, with each source sheet saved as store number, here is an example of one of the formulas.

='Macintosh HD:Users:thomassmedley:Desktop:Weekly Work Project:we010117:[175.xls]Weekly Work'!$C$3:$C$63

The sheet name will always remain constant e.g. 175.xls, however i want to remove the path and final folder and replace it simply with current directory, as this file will sit in the same folder as the source workbooks.

I'm using Excel For Mac 2011, and am really unsure how to do this.

Thanks,

Tom

[FDibbins]

The path will always show, if the file is closed.

this is a link to a closed file...
='C:\Users\Ford\Downloads\excel forum\[testing.xlsm]Formulas 1'!$A$27
and this is that same link when that file is open...
='[testing.xlsm]Formulas 1'!$A$27

[FDibbins]

another duplicate post by the forum

[tsmedley]

For example i give this sheet to my colleague to use and he puts it in a different folder, along with the source files, i want it to link to them, rather than a hardcoded reference e.g. look in the local folder for the files.

[FDibbins]

OK, have you tried to just remove the path?
='[175.xls]Weekly Work'!$C$3:$C$63

[FDibbins]

OK, have you tried to just remove the path?
='[175.xls]Weekly Work'!$C$3:$C$63

[tsmedley]

Yes, if you then move back onto the cell it shows the full path