Forum Discussion

Copper Contributor

Apr 20, 2025

Find and replace mystery

I am attempting to replace all the occurrences of "$23" within a formula with "$43" [I'm not using the quote marks in the box]. Every single time, it replaces it with "$34" instead of "$43". For trou...

excel

Formulas and Functions

office 365

KyleHeck

Copper Contributor

Apr 26, 2025

Sure. Here is the formula in which I am trying to replace that specific text:

=IF('Head-to-Head'!$AF$18=FALSE,0,IF('Head-to-Head'!$AF$18=TRUE,SUMPRODUCT(--(B$32:B$43=B38),--('Head-to-Head'!$AF29<'Head-to-Head'!$AF$23:$AF$34)),0))+
IF('Head-to-Head'!$AG$18=FALSE,0,IF('Head-to-Head'!$AG$18=TRUE,SUMPRODUCT(--(B$32:B$43=B38),--('Head-to-Head'!$AG29<'Head-to-Head'!$AG$23:$AG$34)),0))+

The formula goes on a lot longer, but it's just changes to the column references. For background, it's part of a much larger spreadsheet for keeping up with results/rankings for a kids baseball league.

m_tarler

Bronze Contributor

Apr 28, 2025

What you might be seeing is:

$AG$23:$AG$34 -> $AG$43:$AG$34 -> but that is out of order and gets re-ordered -> $AG$34:$AG$43

so it is replacing it with $43 but then reordered the range to be correct and therefore looks like it was replaced with $34 but it just was re-ordered.