Forum Discussion
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 troubleshooting, I tried:
- Find "23" replace with "43" - it worked correctly.
- Find "$23" replace with "$6300" just to see if a different 'replace with' value would have a different outcome - it still replaces it with "$34"
- I have another find and replace in the same cell and same formula where I am replacing "$18" with "$38" - it works just fine!
I do not have the SUBSTITUTE formula anywhere in this cell. I read somewhere that SUBSTITUTE could affect find and replace.
I am stumped. If it matters, the "$23" I am trying to replace is within a SUMPRODUCT formula. I am using 365.
3 Replies
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?
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.
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.
