Changing a formulae multiple times in excel

[hyoung1445]

A colleague created a spreadsheet which amalgomates data from 12 tabs (Each tab relates to a different date). In the amalgomated spreadsheet each cell relates to a different cell in the tabs. I dont know how she created this originally but I have noticed that in a number of the columns there is an error every fourth row where the forumlae relates to the wrong cell e.g. ='13-01-2014'!$A174 should be ='13-01-2014'!$A$11. Obviously a $ has been missed out therefore the error is not consistent i.e. the next one is ='13-01-2014'!$G177 rather than ='13-01-2014'!$G$11.

Because there error is not consistent i can't simply find and replace. There are apporximately 1500 rows and this error occurs in 5 columns every fourth cell. Is there an easier way of correcting this without doing it manually?

Thanks

 

[dispelthemyth]

Press Ctrl + F then go to replace

in Find what paste ='13-01-2014'!$A*
in replace with paste ='13-01-2014'!$A$11

The above will correct all the incorrect A's references

Repeat for what other formulas are wrong


If you show the VBA code that creates this formula then the change can be made to the code rather than after the fact

 

[hyoung1445]

Unfortunately there are other cells in the spreadsheet that contain references to other cells from the A column. Would Find what ='13-01-2014'!$A* and replace with ='13-01-2014'!$A$11 change all of these?

 

[dispelthemyth]

Is that formula correct and consistent?

if so you can temporarily change this to something else

e.g. before doing what i said in my original reply. Say the formula you don't want to change is ='13-01-2014'!$A$25, then do the find and replace

='13-01-2014'!$A$25 with ='13-01-2014'!$AA$25 and change back later on.