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

[GR]

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:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005
Data.xls]Jan05'!$A2),'Mac HD:Users:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$E2="PA
Shift"),"",'Mac HD:Users:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$A2)

Any ideas?

GR

 

[Bob Greenblatt]

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:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005
Data.xls]Jan05'!$A2),'Mac HD:Users:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$E2="PA
Shift"),"",'Mac HD:Users:geoffreyrenkocuments:WORD
Documents:Schedules:Sign in data & Files:[2005 Data.xls]Jan05'!$A2)

Any ideas?

GR

I think you want to change links, not change the formulas. Choose
Edit-Links-change and navigate to the new workbooks.

 

[GR]

If I do this, will all the cell references be preserved?

 

[Bob Greenblatt]

If I do this, will all the cell references be preserved?

Yes, it just changes the link to point to the correct file. The cell
reference is unchanged.