when this has happened to me it has been a big pain, and happens more often than I would think, even copy/pasting from Apple's own templates after I've filled them out.
Sometimes it is because of the 'relativity' of the function.
- Hover over the color-highlighted cell id (i.e. C7, or B7 in my example) and a little 'circle-down-arrow' will appear (in either the function area under the toolbar, or by bringing up a function pop-up by double clicking a cell).
- click on it and you will see the 'relativity menu' in my image below that begins with:
"(checkmark) (B7) relative"
- You can get more info in help by reading in the built-in help menu:
"Distinguishing Absolute and Relative Cell References"
Sometimes however, the cause has to do with what the 'Cell Format' is in the relative cells in question that the pasted formula would reference
- see the lower left portion of my image below (arrived at by clicking the inspector, then the [42] tab).
I can sometimes, but not always seem to solve my pasted formula woes by one or both of the above.
- Also, just to be sure, the intended newly referenced cells do indeed exist, otherwise my solutions will not be applicable, as there are no such cells to which you can refer in your formulae!
