Find & Replace w/ name change

[ronbo]

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

Thanks for any help.

 

[Tom Ogilvy]

If the workbook you are retrieving values from is always open, you can use
indirect

=Indirect("'[Report " & Text(Date(Year(today()),Month(Today())-1,1),"mmm
yy") & ".xls]Sheet1'!A1")

 

[ronbo]

-----Original Message-----
If the workbook you are retrieving values from is always open, you can use
indirect

=Indirect("'[Report " & Text(Date(Year(today()),Month (Today())-1,1),"mmm
yy") & ".xls]Sheet1'!A1")

--
Regards,
Tom Ogilvy

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

Thanks for any help.

.
Thanks for the help. However, the previous workbook is

never opened with the new month. Is is in the same
directory. Any other ideas???

 

[Tom Ogilvy]

You said:

As simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1".

Which is the formula to link to an open workbook.

So by being imprecise, you wasted a lot of time.

Indirect doesn't work with a closed workbook. You could write a UDF that
starts a new instance of excel, opens the workbook, extracts the data,
closes the workbook and closes the new instance of excel on each recalc of
each instance of the function.

Or you could write a macro that uses the workbook_open event to check the
link and change it when it doesn't match the current month.

--
Regards,
Tom Ogilvy

-----Original Message-----
If the workbook you are retrieving values from is always open, you can use
indirect

=Indirect("'[Report " & Text(Date(Year(today()),Month (Today())-1,1),"mmm
yy") & ".xls]Sheet1'!A1")

--
Regards,
Tom Ogilvy

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

Thanks for any help.

.
Thanks for the help. However, the previous workbook is

never opened with the new month. Is is in the same
directory. Any other ideas???