Why is "Find & Select" function coming up with "the formula you typed contains an error"

Hi,

I have a workbook that has several tabs. The main tabs are the "Data Set" and 4 tabs which are for each financial quarter (Q1, Q2, Q3, Q4). The formulas in Q1 work. The excel tables in Q1 sheet have "Q1" in their name.

I have now copied and pasted the Q1 sheet (which includes formulas) and renamed these sheets "Q2", "Q3", Q4". When I click ctrl H: "find" "Q1" and "replace" with "Q2", I notice the text values of Q1 changes to Q2. However there is also an error "the formula you typed contains an error" and none of the formulas are changed. I manually renamed the tables in Q2 to contain Q2 in case that was the issue. It still didn't work.

General info:

The "Find what" and "Replace with" have "no formula set".
The "match case" and "match entire cell contents" is unchecked.

I am very puzzled on what to do. I definitely do not want to change all the formulas manually as I will likely make a mistake and it will take a LONG time!

Any help is much appreciated.

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

I honestly think it is too complex for me to come up with a dummy set. I tried to explain it the best I could and hopefully someone has come across a similar situation.

at least copy the formula that works in Q1, and perhaps we can show you how to copy it correctly to other Q's

Thanks for your suggestions!! I re-tried trying to do a simple mock sheet but it was too tricky for me as the formulas relate to a variety of different sheets.

I ended up just highlighting and trying "find and replace" at different sections of the worksheet and it worked fine in all the columns when I did it like that.
For some reason I don't understand, when I wanted to do the whole sheet it came up with an error message.
At least I didn't have to change each formula individually!! So hopefully this helps someone else too. (If someone knows why it might do this I would be interested to find out)