Find/Replace all formulas that contain X with Y

[petegrant89]

Hello,

I have a large sheet that contains formulas such as "=SUM('10-20-202-09'!U15,'10-20-202-10'!U15,'10-20-202-11'!U15,#REF!U15,#REF!U15)" in multiple columns. The #REF! error that I am receiving is because the sheet is a template of department sales that have a roll-up to a summary sheet, and each time I use the template there are different amounts of sub-categories and roll-up sheets.

In this case, I need to be able to do a find/replace on "=SUM('10-20-202-09'!U15,'10-20-202-10'!U15,'10-20-202-11'!U15,#REF!U15,#REF!U15)" to replace it with =SUM('10-20-202-09'!U15,'10-20-202-10'!U15,'10-20-202-11'!U15,).

The problem that I am having is that the find/replace function does not take into account that I need this rule to apply to every row in that column, so U15 would become U16,U17, etc. Basically, I would need find/replace to search for "=SUM('10-20-202-09'!X</SPAN>,'10-20-202-10'!X</SPAN>,'10-20-202-11'!X</SPAN>,#REF!X</SPAN>,#REF!X</SPAN>) and replace it with =SUM('10-20-202-09'!Y</SPAN>,'10-20-202-10'!Y</SPAN>,'10-20-202-11'!Y</SPAN>).</SPAN>

 

[RonB1111]

Here's a better methods to sum a cell across multiple sheets. Please see:
Sum the Same Cell in Multiple Microsoft Excel Worksheets