auto file path update when excel sheet moved to another directory.

Hello,
I've been working on a huge excel file for a while and I'm trying to make
it easier to use. My problem is that when it is moved from the template
folder to a working folder the formulas referencing a file in the template
folder have their path changed in the formula and have to have the links
updated. Since the file is protected it's impossible for the users to chance
the source. It's wierd because it will still pull information, but if
information is changed in the external data file the updated doesn't get
ported over.

I need some like the $ command to stop Excel from changing the drive path.
I'd rather have no drive path and have all the reference files open, then the
working directory file is opened. Or statically assign the proper path for
the reference file sitting in the template folder.

Any help, without having to use VB scriping would be apprciated. Thanks

"GNSBoy" wrote:

> Hello,
> I've been working on a huge excel file for a while and I'm trying to make
> it easier to use. My problem is that when it is moved from the template
> folder to a working folder the formulas referencing a file in the template
> folder have their path changed in the formula and have to have the links
> updated. Since the file is protected it's impossible for the users to chance
> the source. It's wierd because it will still pull information, but if
> information is changed in the external data file the updated doesn't get
> ported over.
>
> I need some like the $ command to stop Excel from changing the drive path.
> I'd rather have no drive path and have all the reference files open, then the
> working directory file is opened. Or statically assign the proper path for
> the reference file sitting in the template folder.
>
> Any help, without having to use VB scriping would be apprciated. Thanks
>
='N:\ABC\AnotherDirectory\Main Site\Light\[PARS Template.xls]NARS'!$D$6

I want the path to stay in some cases and in others be the directoy where
the file was opened. The above path actually shouldn't be in front of the
file name because I want it to look at the open file that exists in the
directory. Other links point to a file in a fixed directory and I want to
make the path in front of the file static. EXCEL keeps updating the external
data links!!!! I want it to stop.

Thanks

thanks for your replay my thread.
honestly not like that i mean(access using manual reference link), i have used the formula and now i want to change code with absolut path to vba excel programming like use line code in modules excel. but i no actually no to use the code how and where i begin to write this code.
for all master..please help me for resolving this..

thnks