Find/Replace dialog problem

[cluria]

I have a simple workbook with three sheets. Two of the sheets provide data for calculations on the third sheet. I would like to replace a portion of the formulas in the sheet which calculates. I fill in the replace dialog and click on Find All. I have 12,150 instances of the string which I would like to replace. When I click on Replace All I get an Update Values dialog box which requests that I point to a particular Excel file. I can get rid of the dialog by pressing on the ESC button and then only one of the 12,150 instances is replaced. I don't want to press on ESC 12,150 times. Also I can't understand why I get the Update Values dialog box because my workbook does not link to another Excel file?

I am a beginner with Excel so I hope that there is a solution to this problem via the interface without VBA programming if possible. Please help!

 

[CsJHUN]

Please tell/show us what is in search & replace dialog. If its long please copy paste both here.

 

[cluria]

Press CRTL + H and you will see the dialog box.

Chaim

 

[Fishboy]

Press CRTL + H and you will see the dialog box.

Chaim

I think he means what are you searching for and what are you trying to replace it with

 

[cluria]

Yes I am trying to change all instances of the string MD4 to the string MD2 in the formulas on a specific worksheet.

 

[CsJHUN]

I think he means what are you searching for and what are you trying to replace it with

YES, thank you

Yes I am trying to change all instances of the string MD4 to the string MD2 in the formulas on a specific worksheet.

Try to replace the whole formula not just one part of it.
I mean not only MD4 -> MD2, but for example:
=if(md4=0,"yes","no") -> =if(md2=0,"yes","no")

Or maybe you should check your formulas and make it mixed (both fixed and relative $MD4, or MD$4)

 

[cluria]

I would like to clarify the exact form and function of the formulas. The purpose of the formulas are to move data from one worksheet to another.
They are all of the form: ='MD4'!C20 where MD4 is the name of the source worksheet. I basically want to change the source of the data to another worksheet, MD2.

 

[CsJHUN]

OK, so MD2 and MD4 are wokrsheets...
My advice is still try find and replace as much as you can: 'MD4' to 'MD2'


This is a screenshot from Hungarian excel, but same layout:

Make sure no blank characters before and between the content of the 2 boxes, and your 'MD2' is not 'MD2_' or 'MD2 '.... so on
It must work.

 

[cluria]

I would like to clarify that the Find/Replace dialog succeeds in finding all instances of MD4 on the worksheet. The problems is that when I click on Replace All, the Update Values dialog window appears and only the first instance of MD4 is replaced by MD2. I do not want to replace 12,150 instances of MD4 one at a time!!!!

 

[cluria]

I have solved the problem on my own and would like to thank those who have answered my query. The problem is that when you find/replace the name of a worksheet, that worksheet has to exist previous to the use of the find/replace dialog. When I added a worksheet with the name MD2 all worked fine.