bulk replacement of cell reference within formula
 

In a formula with 8 IF queries, for example
=IF(B13="Blah",Blah!$C$37,IF(B13="Stuff",Stuff!$C$ 37)) etc, how do I replace
all the cell references (say $C$37) in one go, without having to overwrite
each one separately?
I tried highlighting the formula then utilising Find & Replace in the Edit
menu (which has a option to look in formulas) to no avail.
Would appreciate assistance ans there are tonnes to change

Answer: bulk replacement of cell reference within formula
 

1. Select the cell containing the formula you want to modify.
2. Press F2 or double-click on the cell to enter edit mode.
3. Highlight the cell reference you want to replace (e.g. $C$37).
4. Press Ctrl + H to open the Find and Replace dialog box.
5. In the Find what field, enter the cell reference you want to replace (e.g. $C$37).
6. In the Replace with field, enter the new cell reference you want to use (e.g. $D$37).
7. Click on the Options button to expand the dialog box.
8. Select the Look in field and choose Formulas.
9. Click on the Replace All button to replace all instances of the cell reference in the formula.

Excel will replace all instances of the cell reference in the formula with the new cell reference you specified. Repeat these steps for any other cell references you want to replace in the formula.

bulk replacement of cell reference within formula
 

Replace works for me. Don't highlight the formula. Just select any individual
cell or the range in which you want the replacements.
Ensure that Match Entire cell contents is NOT checked and that you are
looking in formulas.
Click on replace all.

Tip: It is a good idea to ensure you have a backup of your workbook when
performing this type of operation in case you get it wrong.

Regards,

OssieMac

"Twishlist" wrote:

In a formula with 8 IF queries, for example
=IF(B13="Blah",Blah!$C$37,IF(B13="Stuff",Stuff!$C$ 37)) etc, how do I replace
all the cell references (say $C$37) in one go, without having to overwrite
each one separately?
I tried highlighting the formula then utilising Find & Replace in the Edit
menu (which has a option to look in formulas) to no avail.
Would appreciate assistance ans there are tonnes to change

bulk replacement of cell reference within formula
 

Thank you for responding...it would appear that highlighting the formula was
indeed the problem.

"OssieMac" wrote:

Replace works for me. Don't highlight the formula. Just select any individual
cell or the range in which you want the replacements.
Ensure that Match Entire cell contents is NOT checked and that you are
looking in formulas.
Click on replace all.

Tip: It is a good idea to ensure you have a backup of your workbook when
performing this type of operation in case you get it wrong.

Regards,

OssieMac

"Twishlist" wrote:

In a formula with 8 IF queries, for example
=IF(B13="Blah",Blah!$C$37,IF(B13="Stuff",Stuff!$C$ 37)) etc, how do I replace
all the cell references (say $C$37) in one go, without having to overwrite
each one separately?
I tried highlighting the formula then utilising Find & Replace in the Edit
menu (which has a option to look in formulas) to no avail.
Would appreciate assistance ans there are tonnes to change