Excel

  • Thread starter Thread starter Eileen Pagliara
  • Start date Start date
E

Eileen Pagliara

I am running XP. I have a workbook consisiting of 12
worksheets (one for each month). In each worksheet there
are roughly 150 cells that are linked to another workbook.
At the beginning of every new year, I need to change a
portion of the formula in each of these cells.
Here's an example of a cell formula:
='J:\STP RPTS\[STP 2003.xls]February, 2003'!$F$6
I need to change both 2003's to 2004.
When I go to Edit and Replace and say Replace All, it
won't automatically replace them all on its own. It pulls
up a window where I have to click on the drive the linked
file is in, then a window for the folder the linked file
is in, then the file itself. This has to be done 150
times in each worksheet.
This is very time consuming. Is there a faster, easier
way to change all the 2003's to 2004?
Any help will be greatly appreciated. Thank you in
advance.

Sincerely,
Eileen Pagliara
 
Eileen

Edit>Replace

what: =
with: xx=

Choose "Workbook" from the Options>"within" dialog

Now do your replace 2003 with 2004 across the workbook.

If you have no other dates that would be affected simply Replace All.

If you have other 2003 dates you want to keep, you would have to selectively
replace one at a time when going from 2003 to 2004.

When complete, reverse the process of xx= replace.

Gord Dibben XL2002
 
Eileen,

I have that situation happen quite often to me.
Generally, Excel pulls up the extra dialogue box when I
haven't created the files to which I am linking. If the
files aren't created, it can't figure out what you're
trying to link to.

However, there are times when I am POSITIVE that I have
the other files created. At those times, I just cheat and
open those files before I do the find/replace. That isn't
practical if I were linking to 1,000 files, but with only
10-20 files, it's an easy way for me to avoid it.

Hope that helps?

Eric
 
Back
Top