Excel linking from multiple documents and altering 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: If I had 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, 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.

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

Meghan,

Before you do the following make backups of all your files. You MUST do this.

1. Open up the main file (Sears Tower). 3. Open up one of your other files (i.e. April) 4. Select "Window" from the main menu 5. Select "Arrange" from the drop down menu 6. Select "Tiled"

You will now have both files showing on your screen

7. Click on the "April" file 8. Click on the tab on the bottom of the "April" file and hold the mouse button down until a small box appears on the mouse. This means you can drag the tab to the main file (Sears Tower). 9. Still holding the mouse button down drag the tab over to the tabs on the main file (Sears Tower). It must be over the tabs and not anywhere else on the file. 10. Release the mouse button. This will drop the tab into the main file (Sears Tower) 11. Close the "April" file and do not save 12. Save the main file (Sears Tower)

Do this again and again until you have migrated all of the other files over to the main file (Sears Tower). Now all of the information is linked and all changes will update automatically. When you want to start a new file simply save the file under a new name.