Find/Replace within a formula

[jk4747]

Hello, I have the following function in Excel that references tables from different sheets.

=SUMIF(ParentTCA[$ Weighted %Placement],"<>#VALUE!")/SUMIF(ParentTCA[$ Weighted PWP20%],"<>#VALUE!",ParentTCA[ValueUSD])

I need to replace every "ParentTCA" with "TCA" but when I run a find and replace I get an error message saying "There's a problem with this formula". I have tried running the SUBSTITUTE function without any luck. Please help!

 

[JamesCanale]

Go to Formulas and Name Manager, find the table named ParentTCA and double click on it. Then type in the new name - TCA. It will change your formula.

 

[jk4747]

Sorry, I should've been more clear--I don't need to just change the name, I need the formula to reference a separate table that happens to be titled "TCA".

 

[JamesCanale]

The two tables got to have the same column headers. Then the find and replace will work.

So both ParentTCA and TCA have to have column headers - $ Weighted %Placement and $ Weighted PWP20% and ValueUSD

 

[jk4747]

That was it, thank you!