Help with linked formula

[somedaymaybe]

Hi All,

First time posting here, which I must say is a credit to all as I have always managed to resolve my issues using previous threads although this one has me stumped.

I work for project management company which means that every project has the same forms which need to be completed through all stages of the project life cycle, there is inevitably a lot of repetition. I have been asked to create a folder structure, within which template forms are kept. The idea is that a master form will be created when a project starts and the info in this will carry through to all the templates.

my folder structure so far looks like:
- server\master templates\Project number XXXX_Project Name:
- Tenders
- Project set up
- Site Forms
- Master Project File.xls

The folder and the templates will then be moved and the folder renamed for each project.

I managed to get all the links from the template files to link to the master form correctly and all woks great. When I move the folder and rename it for a new project all the links keep pulling the data from the original file.

Is there any way to resolve this so that each time the folder is copied the formulas update automatically? There are about 100 forms each with at least 4 links so pretty laborious to update manually.

Thanks in advance for the help.

 

[chirayu]

More than likely the formula in the file has the path in front of it like

C:\Users\Chirayu\Documents\[Excel Sample file.xlsm]Sheet1'!$A$1

In your case its probably the server name and folder name as listed above in your post. You need to remove this part from all formulas in your file.

That's the problem with linking files. It will also link to original path. Just open one formula & copy the path out of it - leave filename/range intact, then select the entire range of formulas & find replace the path with nothing. Should fix it