Changing Global References to Local

[ASHayward]

Hi Guys,

I'm currently creating a schedule that has a workbook that references many other different workbooks...The only issue is that this schedule is needed for many projects, so I copy & paste a 'master' root folder that contains all the workbooks to a new file location on the computer to use for that specific project.

Each time I do this the cell references still use the original master workbooks, because they use absolute references opposed to local ones. And I haven't figured out how to have them automatically update so they don't use :U\\folder1\folder2\etc... and instead use /folder2/folder1\folderA\etc...

Hyperlinks seem to update fine, it's just the references in the cells that I can't get to change.

Thanks in advance!

Alistair

[Pete_UK]

You could use Find & Replace (CTRL-H) to change the original folder to the new one.

Hope this helps.

Pete

[ASHayward]

Hi Pete,

Cheers for getting back to me, unfortunately there are many different references I will have to change, so 'Find & Replace' won't be that feasible. (unless I want to spend a few hours every time we get a new project!). Please see attached for an example of the kind of path I have at the moment:

Capture.PNG

and the kind of reference I would prefer to have:

Capture.PNG

When I use what I would've thought to be a way round it, the cell kicks out a #REF.

Am I getting the syntax wrong or something?

Cheers,

Alistair

[Pete_UK]

I can't view .png files on this forum due to software incompatibilities with some browsers, so your response doesn't mean anything to me.

Pete

[Aardigspook]

Have you tried using the 'Edit Links' option (in the 'Connections' section of the 'Data' tab)? That should allow you to change all references to one file, to another file.

[ASHayward]

Pete,

Apologies for that, please find attached the two files I attached previously:

Kind of reference I have now:

Capture.JPG

The kind of reference I would like:

Capture 2.JPG

Aardingspook, thanks for the suggestion, you're right, I could do that. However (if possible) I would like the links to update automatically every time I copy and paste this file from my 'master file location' to a new location on the computer/drive.

If there's a way to keep a file link, using the file folder location as the root folder instead of the drive. i.e:

folder3/folder2/foldera\folderb

instead of:

U:\folder1\folder2\foldera\folder

Thanks in advance,

Alistair

[ASHayward]

Bump......