so I have to change each formula by hand

There is no way to do what you want. The INDIRECT function is generally the
way to do this but it works only for references within a workbook, not to
other workbooks.

How about Find/Replace, like replacing "Month 6" with "Month 7". Quicker
than manual.

--
Jim
"Iain Mac" <Iain wrote in message
...
| Hello all
|
| I hope my question makes sence
|
| I have a large number of workbooks containing a varying number of sheets,
| which are updated each month by people. One part of the sheet references
| back to the previous month's to obtain the value of a specific cell
|
| My problem is this. Each month I have to create a new set of workbooks in
a
| new folder (Called Month XX), but obviously the formula looking back to
the
| previous months cell is now looking in the wrong folder and so I have to
| change each formula by hand to point it to the new folder (This is Month
7,
| the workbooks in Month 6 are referenced to Month 5, but copied now into
Month
| 7 are STILL referenced to Month 5)
|
| What I thought of doing was have the folder name, i.e Month XX, as a text
| string in a cell (which can simply be changed each month) and have the
| formula pick that value up and use it. My formula is currently:
|
| ='\\W2knas1\com-bm$\2006-2007\Month 6\[Assessment.xls] 4180 - Fieldwork
| Teams'!$G$13
|
| I want the \Month 6\ part of the formula (which is part of the external
| reference path) to get that little piece of information form say Sheet1!A1
|
| Then in Sheet1!A1 I can type Month 6, then next month type Month 7 and the
| formulas all look to month 7
|
| I can not seem to work out how to do this (if it is at all possible to)
and
| so would appreciate any suggestions
|
| Thank you