Pro Tip: copy an entire worksheet and extract a difficult formula instead of trying to copy it.
You’ve got a very nice spreadsheet with some pretty nasty formulas. They are contained within a column of data but go here there and everywhere else in between. You know that if you just copy the formulas, they will re-reference and breka themselves. You also know that they are not using dollar sign references correctly… since you never expected to copy them. The solution:
This trick works because Excel will not update references when copying cells across worksheets. This means you will get the formuals exactly as before without having to go add dollar signs everywhere.
There is some version of this trick that works for nearly all formulas copy/paste issues. The trick is usually thinking through how the re-reference will happen to work out the best steps. Sometimes, it’s not possible to get dollar signs that do everything exactly right. This tool should always be in your box.