Problem: I need to make an exact copy of a range of formulas. I do NOT want the cell references to change as I copy. Whoever set up the worksheet did not include dollar signs in the formulas. It is like I want to do a cut and paste, but keep the original formulas there.
This is a common problem. If you copy and paste, the formula references will change. If you cut and paste, the formula references keep pointing at the same place. But, with a cut and paste, the original formulas are no longer there.
Strategy: Use Find and Replace to replace the leading equals sign with a word. This changes the formulas to text. Copy the text to the new location, then use Find and Replace to change the word back to an equals sign. You will now have two identical sets of formulae.
- Replace = with any word.
After doing that replace, you have text versions of the formulas. Copy the text to a new place.
Use Find and Replace on both the original and copied range to change the word back to an equals sign.
- Change the text back to formulas.
- You have an exact copy of the original formulas.