Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Brass Contributor

Hello,

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

@Michael1105 

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)
Solution

@Michael1105 

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

S2243.png

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. :)
1 best response

Accepted Solutions
best response confirmed by Michael1105 (Brass Contributor)
Solution

@Michael1105 

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

S2243.png

View solution in original post