Updating worksheets with external file references

[kneelab]

I have a worksheet (the MAIN worksheet) that is filled (using formulas) with values that originate in other worksheets (the CHILDREN worksheets). Each cell in the MAIN worksheet refers to a cell in one of the CHILDREN. Typically, I have 1 MAIN worksheet and 90 CHILDREN worksheets.

I would like to change the formula in each cell in the main worksheet to refer to a new set of children worksheets. For example, I might have a set of children worksheets in a folder called "DAY1" and I might want to replace DAY1 with DAY2 in all the formulas to link to a different set of children worksheets.

The problem is I have to open all the the children worksheets (90 of them) to update the cells in the MAIN worksheet. Then I have to close all 90 of the children worksheets. This takes a long time, considering I have many many files like this.

Does anyone have ideas how I can update cell formulas to worksheets that are not open?

[JBeaucaire]

A) Using SEARCH/REPLACE

1) Highlight the range to adjust
2) activate the search/replace function (CTRL-H)
3) click on Options
4) make sure you are searching in formulas
5) make sure you are not matching entire cells

With that you should be able to change references or part of refernces without opening the external workbooks.


B) USING LINKS

On the EDIT menu is a LINKS menu. You can use that interface to change any one of your external links.