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

HansVogelaar

MVP

Apr 21, 2025

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?

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.