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

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

In , Jim Rech
spake thusly:

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.

See Laurent Longre's excellent "Morefunc" UDF collection
at http://xcell05.free.fr/ . Among functions in the collection
is INDIRECT.EXT, which, he says, functions the "same as INDIRECT
working also with closed workbooks."

-dman-

Create 2 folders one "Current Month" and the other "Prior Month"
In the current month make your foumulas look to the Prior Month folder.
When the current month is complete save a copy of the file in the Prior
Month folder.
Continue to update the file in the current month and you won't have to
change the formulas.


"Iain Mac" wrote:

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

Good thinking Florida... At the end of the month make a copy of "Prior Month"
and rename it to the month it reflects before replacing it with "Current
Month".

"Florida User" wrote:

Create 2 folders one "Current Month" and the other "Prior Month"
In the current month make your foumulas look to the Prior Month folder.
When the current month is complete save a copy of the file in the Prior
Month folder.
Continue to update the file in the current month and you won't have to
change the formulas.


"Iain Mac" wrote:

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