Help! formulas, linking, and naming conventions

and in the C Drive I have a the main (mother-hub folder) called

_______________________________________________________________

• • Sears Tower (Main ¡°mother ¨Cfolder) (folder)

Invoice (sub-folder) - April (excel file) - May (excel file) - June (excel file) - July (excel file)

	Billing (sub-folder)

- food (excel file)

- entertainment (excel file)

- transportation (excel file)

Planning (sub-folder)

- Update report (excel file)

_________________________________________________________________

The question:

              Now that I mapped it out, my question is  thefiles are linked to one another using formulas. Example, if in the Planning (sub-folder) the update report was comprised of hundreds of different cells on theseworksheets reading other numbers and calculating numbers from all the information that is linked from all thefiles that come from the Invoice (sub-folder). 

If it makes a difference, an example of a formula would look like this in one of the cells in the Update report;

IF((MATCH(A$3,'M:\Sears Tower\Invoice\[April.XLS]Summary'!$A:$A,0)>0),LOOKUP(A$3,' M:\Sears Tower\Invoice\[April.XLS]Summary'!$A:$A, M:\Sears Tower\Invoice\[April.XLS]Summary'!$D:$D))

So my question: I have to change the name from Sears Tower to Willis Tower. How can I change the name of the ¡°mother-folder¡± from Sears Tower to Willis Tower when all the files that are in other sub-folders under Sears Tower have formulas with Sears Tower in it! ??

I had assumed that formulas would automatically update and change the name in each formula when the named changed but they didn¡¯t, But I have tried everything from keeping excels docs open and changing the name and then closing it, and vice versa. I brought everything that was in the sub-folders, out to be directly under the ¡°mother-folder¡± when it was Sears Tower. Then changed it to Willis Tower and still no change. The formulas in those sub-folders that I listed in my example don't recognize the change of the main folder when I change it from Sears Tower to Willis Tower. As a result of that, when I want to keep updating and add new data to thesheets, they won't link from one to the other (because it can¡¯t locate it, since there is no more SEARS Tower folder) and shows an error since they don't recognize it.

This is a huge problem for my company and we have history going back to 1980s with client¡¯s history and data and we couldn¡¯t just ¡°re-do¡± everything to change a file name.

PLEASE I would do anything for you help. If you don¡¯t know what needs to be done to change a main folder name and still have linking formulas update and link, would you know of any other source that I can turn to? I have been going at this for quite sometime and it seems like no one has any idea.

Please keep me posted at your earliest convenience.

Meghan

AnswerEdit

Wow this was so long - I feel like maybe you should call me instead! LOL ...

Okay so am I understanding it right, that you're inquiry about this more like an ongoing problem. Not just one time. You want to know how you can change the folder names, which essentially changes the true path of thefiles, without upsetting all the links.

I am sure you have already thought through the idea of Find and Replace "Sears" with "Willis", and isn't the problem with that, the formulas turn to errors before you can find and replace?

The only way I have come close to this is by basically puttingin a coma, by turning automatic calculation off.

Like this.

BEFORE you change the name of the folder (which will mess up your links), open the destination file with the links. Turn automatic calculation OFF. Save and close.

Change the name of the folder.

Reopen the destination file with the links. Check to make sure automatic calculation is still off. Find and Replace Sears with Willis.

Turn automatic calculation back on.

If this doesn't work, post back, and I have one more idea. It's untested, but it has to do with formatting 100% of your formula cells as text. (which hopefully will stop them from thinking they are links and going to error)., then changing the name of the folder, then find and replace, then turn them all from text to formula again, which may require some code. Which I have yet to find, but I'm relatively sure it's out there.