Find and Replace within a formula?

[olimajor123]

Hi there

Is it possible to find and replace within a formula? IE I have a formula which is a SUMIF and is looking up on 30+ sheets, I want to copy the formula but want to change column N to column X. Is there a way to do this quickly in one go do I have to do it manually?

Thanks

 

[gaz_chops]

You could use Find/Replace, what version of Excel are you using?

 

[olimajor123]

You could use Find/Replace, what version of Excel are you using?

I have tried to use that but it is applying it to every formula in the worksheet - Is there a way to change it so it only does it in that cell?

 

[gaz_chops]

what is the formula you need to change?

 

[jamilm]

Select that range before activating the Replace dialog

 

[olimajor123]

The formula is =(SUMIF(CH!$B$5:$B$67,SUMMARY!$D5,CH!BN$5:BN$67)+SUMIF(KC!$B$5:$B$67,SUMMARY!$D5,KC!BN$5:BN$67)+SUMIF(SH!$B$5:$B$67,SUMMARY!$D5,SH!BN$5:BN$67)+SUMIF(PC!$B$5:$B$67,$D5,PC!BN$5:BN$67)+SUMIF(PC!$B$5:$B$67,$D5,PC!BN$5:BN$67)+SUMIF(DW!$B$5:$B$67,$D5,DW!BN$5:BN$67)+SUMIF(CF!$B$5:$B$67,$D5,CF!BN$5:BN$67)+SUMIF(SM!$B$5:$B$67,$D5,SM!BN$5:BN$67)+SUMIF(PS!$B$5:$B$67,$D5,PS!BN$5:BN$67)+SUMIF(MM!$B$5:$B$67,$D5,MM!BN$5:BN$67)+SUMIF(MK!$B$5:$B$67,$D5,MK!BN$5:BN$67)+SUMIF(GR!$B$5:$B$67,$D5,GR!BN$5:BN$67)+SUMIF(RP!$B$5:$B$67,$D5,RP!BN$5:BN$67)+SUMIF(PV!$B$5:$B$67,$D5,PV!BN$5:BN$67)+SUMIF(MS!$B$5:$B$67,$D5,MS!BN$5:BN$67)+SUMIF(PL!$B$5:$B$67,$D5,PL!BN$5:BN$67)+SUMIF(PG!$B$5:$B$67,$D5,PG!BN$5:BN$67)+SUMIF(CJ!$B$5:$B$67,$D5,CJ!BN$5:BN$67)+SUMIF(GW!$B$5:$B$67,$D5,GW!BN$5:BN$67)+SUMIF(JS!$B$5:$B$67,$D5,DK!BN$5:BN$67)+SUMIF(JW!$B$5:$B$67,$D5,JW!BN$5:BN$67)+SUMIF(DDV!$B$5:$B$67,$D5,DDV!BN$5:BN$67)+SUMIF(AL!$B$5:$B$67,$D5,AL!BN$5:BN$67)+SUMIF(EL!$B$5:$B$67,$D5,EL!BN$5:BN$67)+SUMIF(DaW!$B$5:$B$67,$D5,DaW!BN$5:BN$67)+SUMIF(DR!$B$5:$B$67,$D5,DR!BN$5:BN$67)+SUMIF(NC!$B$5:$B$67,$D5,NC!BN$5:BN$67)+SUMIF(PD!$B$5:$B$67,$D5,PD!BN$5:BN$67)+SUMIF(MiK!$B$5:$B$67,$D5,MiK!BN$5:BN$67)+SUMIF(RL!$B$5:$B$67,$D5,RL!BN$5:BN$67)+SUMIF(MMC!$B$5:$B$67,$D5,MMC!BN$5:BN$67)+SUMIF(TP!$B$5:$B$67,$D5,TP!BN$5:BN$67)+SUMIF(DQ!$B$5:$B$67,$D5,DQ!BN$5:BN$67)+SUMIF(LR!$B$5:$B$67,$D5,LR!BN$5:BN$67)+SUMIF(AS!$B$5:$B$67,$D5,AS!BN$5:BN$67)+SUMIF(MSt!$B$5:$B$67,$D5,MSt!BN$5:BN$67)+SUMIF(NS!$B$5:$B$67,$D5,NS!BN$5:BN$67)+SUMIF(DS!$B$5:$B$67,$D5,DS!BN$5:BN$67)+SUMIF(GWA!$B$5:$B$67,$D5,GWA!BN$5:BN$67)+SUMIF(NH!$B$5:$B$67,$D5,NH!BN$5:BN$67)+SUMIF(LP!$B$5:$B$67,$D5,LP!BN$5:BN$67)+SUMIF(GS!$B$5:$B$67,$D5,GS!BN$5:BN$67)+SUMIF(TS!$B$5:$B$67,$D5,TS!BN$5:BN$67)+SUMIF(CL!$B$5:$B$67,$D5,CL!BN$5:BN$67)+SUMIF(DL!$B$5:$B$67,$D5,DL!BN$5:BN$67)+SUMIF(JH!$B$5:$B$67,$D5,JH!BN$5:BN$67)+SUMIF(PH!$B$5:$B$67,$D5,PH!BN$5:BN$67)+SUMIF(AB!$B$5:$B$67,$D5,AB!BN$5:BN$67)+SUMIF(CA!$B$5:$B$67,$D5,CA!BN$5:BN$67)+SUMIF(AG!$B$5:$B$67,$D5,AG!BN$5:BN$67)+SUMIF(MC!$B$5:$B$67,$D5,MC!BN$5:BN$67))5:BN$67)+SUMIF(SM!$B$5:$B$67,$D5,SM!BN$5:BN$67)+SUMIF(PS!$B$5:$B$67,$D5,PS!BN$5:BN$67)+SUMIF(MM!$B$5:$B$67,$D5,MM!BN$5:BN$67)+SUMIF(MK!$B$5:$B$67,$D5,MK!BN$5:BN$67)+SUMIF(GR!$B$5:$B$67,$D5,GR!BN$5:BN$67)+SUMIF(RP!$B$5:$B$67,$D5,RP!BN$5:BN$67)+SUMIF(PV!$B$5:$B$67,$D5,PV!BN$5:BN$67)+SUMIF(MS!$B$5:$B$67,$D5,MS!BN$5:BN$67)+SUMIF(PL!$B$5:$B$67,$D5,PL!BN$5:BN$67)+SUMIF(PG!$B$5:$B$67,$D5,PG!BN$5:BN$67)+SUMIF(CJ!$B$5:$B$67,$D5,CJ!BN$5:BN$67)+SUMIF(GW!$B$5:$B$67,$D5,GW!BN$5:BN$67)+SUMIF(JS!$B$5:$B$67,$D5,DK!BN$5:BN$67)+SUMIF(JW!$B$5:$B$67,$D5,JW!BN$5:BN$67)+SUMIF(DDV!$B$5:$B$67,$D5,DDV!BN$5:BN$67)+SUMIF(AL!$B$5:$B$67,$D5,AL!BN$5:BN$67)+SUMIF(EL!$B$5:$B$67,$D5,EL!BN$5:BN$67)+SUMIF(DaW!$B$5:$B$67,$D5,DaW!BN$5:BN$67)+SUMIF(DR!$B$5:$B$67,$D5,DR!BN$5:BN$67)+SUMIF(NC!$B$5:$B$67,$D5,NC!BN$5:BN$67)+SUMIF(PD!$B$5:$B$67,$D5,PD!BN$5:BN$67)+SUMIF(MiK!$B$5:$B$67,$D5,MiK!BN$5:BN$67)+SUMIF(RL!$B$5:$B$67,$D5,RL!BN$5:BN$67)+SUMIF(MMC!$B$5:$B$67,$D5,MMC!BN$5:BN$67)+SUMIF(TP!$B$5:$B$67,$D5,TP!BN$5:BN$67)+SUMIF(DQ!$B$5:$B$67,$D5,DQ!BN$5:BN$67)+SUMIF(LR!$B$5:$B$67,$D5,LR!BN$5:BN$67)+SUMIF(AS!$B$5:$B$67,$D5,AS!BN$5:BN$67)+SUMIF(MSt!$B$5:$B$67,$D5,MSt!BN$5:BN$67)+SUMIF(NS!$B$5:$B$67,$D5,NS!BN$5:BN$67)+SUMIF(DS!$B$5:$B$67,$D5,DS!BN$5:BN$67)+SUMIF(GWA!$B$5:$B$67,$D5,GWA!BN$5:BN$67)+SUMIF(NH!$B$5:$B$67,$D5,NH!BN$5:BN$67)+SUMIF(LP!$B$5:$B$67,$D5,LP!BN$5:BN$67)+SUMIF(GS!$B$5:$B$67,$D5,GS!BN$5:BN$67)+SUMIF(TS!$B$5:$B$67,$D5,TS!BN$5:BN$67)+SUMIF(CL!$B$5:$B$67,$D5,CL!BN$5:BN$67)+SUMIF(DL!$B$5:$B$67,$D5,DL!BN$5:BN$67)+SUMIF(JH!$B$5:$B$67,$D5,JH!BN$5:BN$67)+SUMIF(PH!$B$5:$B$67,$D5,PH!BN$5:BN$67)+SUMIF(AB!$B$5:$B$67,$D5,AB!BN$5:BN$67)+SUMIF(CA!$B$5:$B$67,$D5,CA!BN$5:BN$67)+SUMIF(AG!$B$5:$B$67,$D5,AG!BN$5:BN$67)+SUMIF(MC!$B$5:$B$67,$D5,MC!BN$5:BN$67))

The BN needs to change to BV

Everything else stay the same

I did try and just highlight the formula in the text box and do it through there but it changed all formulas on the spreadsheet

 

[gaz_chops]

Wow! How many occurrences of this formula are there?

 

[olimajor123]

Quite a few, but the items that need changing refer to months on the other sheets, so only really need to make the changes 12 times for different cells but obviously would rather not do it manually!

 

[gaz_chops]

Well you could try Highlighting the 1st cell with the formula, then Find/Replace, BUT select Replace NOT Replace all. Then Close and highlight next cell etc

 

[AkaTrouble]

in this case and with the size of the formula think i would copy it to notepad or new temp sheet do the find and replace and copy back corrected formula