Find/Search in Formulas - won't find phrase after copying/renaming sheet/book

I have two workbooks - one with data downloaded from an external source
into a sheet for each of the 12 months - another with sheets that refer
to that data and perform calulations, etc. for each month. I need to
create two new sheets to use in 2006 instead of 2005, so I want to
rename the workbooks, the sheets, and all the references in the cells -
basically just replace 2005 with 2006 at all levels.

I did a <Save As> for each workbook with the new year in the name, and
went into the data workbook and renamed the <Jan05> sheet to <Jan06>.
Then I went into the calculation workbook to the Jan sheet, pulled up
the <Replace> dialog (making sure the <look in> menu was set to
<formulas> and the <match entire cell> box is unchecked) and entered
the phrase '[2005 Data.xls]Jan05' in the <look for> box and '[2006
Data.xls]Jan06' in the <replace with> box.

Excel says it cannot find it. I have checked to be sure there are no
typos, spaces added/omitted, etc. but can't figure out why it won't
find it. I tried opening up the original 2005 workbook and the same is
true - it can't find the '[2005 Data.xls]Jan05' phrase either.
Interestingly, if I go in and manually change one of the occurrences in
one of the cells, then it CAN find the occurrences IN THAT MODIFIED
CELL ONLY. Weird.

An example of the contents of the cell where it should be able to find
the phrase is:

=IF(OR(ISBLANK('Mac HD:Users:geoffreyrenk:Documents:WORD
Documents:Schedules:Sign in data & Files:[2005
Data.xls]Jan05'!$A2),'Mac HD:Users:geoffreyrenk:Documents:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$E2="PA
Shift"),"",'Mac HD:Users:geoffreyrenk:Documents:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$A2)

Any ideas?

GR

Just a guess.

Did you include the leading and trailing apostrophes when you did the
edit|Replace.

'[2005 Data.xls]Jan05'

Or was that just in the post?

If you did include them, try it without including them.

GR wrote:
>
> I have two workbooks - one with data downloaded from an external source
> into a sheet for each of the 12 months - another with sheets that refer
> to that data and perform calulations, etc. for each month. I need to
> create two new sheets to use in 2006 instead of 2005, so I want to
> rename the workbooks, the sheets, and all the references in the cells -
> basically just replace 2005 with 2006 at all levels.
>
> I did a <Save As> for each workbook with the new year in the name, and
> went into the data workbook and renamed the <Jan05> sheet to <Jan06>.
> Then I went into the calculation workbook to the Jan sheet, pulled up
> the <Replace> dialog (making sure the <look in> menu was set to
> <formulas> and the <match entire cell> box is unchecked) and entered
> the phrase '[2005 Data.xls]Jan05' in the <look for> box and '[2006
> Data.xls]Jan06' in the <replace with> box.
>
> Excel says it cannot find it. I have checked to be sure there are no
> typos, spaces added/omitted, etc. but can't figure out why it won't
> find it. I tried opening up the original 2005 workbook and the same is
> true - it can't find the '[2005 Data.xls]Jan05' phrase either.
> Interestingly, if I go in and manually change one of the occurrences in
> one of the cells, then it CAN find the occurrences IN THAT MODIFIED
> CELL ONLY. Weird.
>
> An example of the contents of the cell where it should be able to find
> the phrase is:
>
> =IF(OR(ISBLANK('Mac HD:Users:geoffreyrenk:Documents:WORD
> Documents:Schedules:Sign in data & Files:[2005
> Data.xls]Jan05'!$A2),'Mac HD:Users:geoffreyrenk:Documents:WORD
> Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$E2="PA
> Shift"),"",'Mac HD:Users:geoffreyrenk:Documents:WORD
> Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$A2)
>
> Any ideas?
>
> GR

--

Dave Peterson