difficult find and replace

[amartino44]

Hello. I have these formulas (there are 100+ but all of the same pattern) down a column.

=SUM('ACTUALS detail'!AM72:AQ72)
=SUM('ACTUALS detail'!AM73:AQ73)
=SUM('ACTUALS detail'!AM87:AQ87)
=SUM('ACTUALS detail'!AM92:AQ92,'ACTUALS detail'!AM93:AQ93)

How can I do find and replaces to make the formulas like this:
=SUM(Indirect("'ACTUALS detail'!AM72:"&AT&"72")
and for the ones with multiple selections
=SUM(Indirect("'ACTUALS detail'!AM92:"&AT&"92,ACTUALS detail'!AM93:"&AT&"93")

I don't want have to do that manually for each cell. Thanks.

 

[Chrisdontm]

It looks like three times will do it...

Find:
SUM(
Replace with:
SUM(INDIRECT("

Find:
:AQ
Replace with:
:"&AT&"

Find:
)
Replace with:
")

☺

 

[amartino44]

Oh, thanks!! The only problem is that when I do the find and replace one at a time, it won't allow me to insert into the formula. It says formula error and then doesn't make the changes. Thanks.

It looks like three times will do it...

Find:
SUM(
Replace with:
SUM(INDIRECT("

Find:
:AQ
Replace with:
:"&AT&"

Find:
)
Replace with:
")

☺

 

[Chrisdontm]

I believe it is saying "formula error" because when you add in the INDIRECT it does make it a formula error.

One thing I noticed was that you would then need a closing parenthesis...

Another thing is that I don't think that the syntax is right the way you added in the INDIRECT function...

I am not too experienced with that function...☺☻

But when I used a simple function, I was able to Find & Replace all parts of it...