# difficult find and replace

#### amartino44

##### Board Regular
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
It looks like three times will do it...

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

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

Find:
)
Replace with:
")

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:
")

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...

Replies
16
Views
1K
Replies
0
Views
436
Replies
0
Views
451
Replies
1
Views
193
Replies
2
Views
853

1,196,498
Messages
6,015,557
Members
441,901
Latest member
joshtfin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back