Why is Find/Replace saying "There's a problem with this formula"?

Brass Contributor


I am boggled by this and it's hindering me from replacing something within a formula across a worksheet that should be fairly simple of a process to complete.


I have this formula in a cell that refers to a table within another worksheet:  =sumifs(Jan5Wk1[Amount],Jan5Wk1[Product],"A*") 


I am trying to do a Find/Replace to change "Jan5" to "Jan6". 

When I click "Replace" within the cell I am returned with the "There's a problem with this formula. Not trying to type a formula?...etc" error.


First, I am trying to type a formula.  


Second, if I manually type the formula =sumifs(Jan6wk1[Amount],Jan6wk1[Product],"A*") Excel accepts it.  

I have tried entering the entire formula in for both the find and replace and that returns the same error. 


I don't want to have to go to each cell and type this formula when the formula is already intact and the only part of the formula I need to change is the "Jan5 to Jan6".  It will take me forever to complete this project if I have to do this plus it leaves room for error in inputting this formula in each cell.  I have 5 worksheets to change like this in a total of 1200 cells.


What is wrong with Excel that it is returning this error?  What am I doing wrong that would cause this?

Is there another way to find and replace data in a cell other than the Find/Replace feature?


I appreciate any help you can lend with this situation.

4 Replies


In a quick test it works as intended for me.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar
I want to thank for your response. 

I've attached the file and the screen shot.

best response confirmed by Michael1105 (Brass Contributor)


Thanks. The cause of the problem is that jun6wk3 doesn't have an Amount column:


Thank you. After fixed this, I've also realized that some of the tables weren't labeled correctly.
I appreciate your time and your assitance. :)