find and replace annoying error

[charchar]

I don't know how you guys handle this problem.

Once in a while, I need to replace the links in the 1000 cells from one files to another file. I use replace. If I type incorrectly the new links, then the excel will open a standard "Openning a file" window for EVERY cells. In my case, locating a same file for 1000 times is gonna kill me. But I don't know how to get around this annoying problem but to kill the excel process without saving it. Sometime, I have done so much work but I have to do it again.

Anyone has any better way?

Thanks

 

[just_jon]

Click Edit | Links , then select the link to change and click Change Source.

Navigate to the correct file,then click OK

 

[charchar]

Click Edit | Links , then select the link to change and click Change Source.

Navigate to the correct file,then click OK

I think I don't express myself correctly. When you use Edit-Replace, put the old link in "Find What" and put the new link in the "Replace Which", if you type any letter wrong in new link, the excel won't give you control to do anything but just open a window for you to locate the right files 1000 times. In that situation, you totally lost control of excel. My question is how to get control of excel in that situation withiout terminating excel program but task manager.

Thanks

 

[just_jon]

Don't know, but holding down the Escape key will cycle through the prompts fairly quickly.

I'll see if I can find out.

 

[erik.van.geit]

charchar,

if I do understand what you need ...
couldn't you put the common part of those cells within one cell ?
if you put in A1 the workbookname + worksheetname like this
[Book1.xls]reports!
you can refer to A1
using =INDIRECT(A1&"$A$1")

does this help ?
kind regards,
Erik

 

[charchar]

charchar,

if I do understand what you need ...
couldn't you put the common part of those cells within one cell ?
if you put in A1 the workbookname + worksheetname like this
[Book1.xls]reports!
you can refer to A1
using =INDIRECT(A1&"$A$1")

does this help ?
kind regards,
Erik

Erik, this is a good idea and I am going to try this. The only problem is people in our company like to use the old-fashion way. They want to see the direct link in the cell to know where the value comes from. Hopefully, I won't let them be panic when they see indirect function.

Thank you very much.

charchar

 

[erik.van.geit]

a bit more work could be then to rename the cell A1 with the workbookname
then find-replace A1 with this new name each time you do the operation

=INDIRECT(Workbook1 &"$A$1")
workbook1 is the name of cell A1

best regards,
Erik

 

[just_jon]

I suppose you could use a macro to do it, using Find/Replace on formula cells; that way you could terminate the code on the 1st error.

But I can't imagine that's a practical idea as opposed to the occaisonal typo ...