How do I enter variable data into a formula
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
|