Forum Discussion
Solved
Paste formulas not working
If I copy a value that has been generated by a formula in Excel and paste into a different cell and then try to convert the value in the cell to appear as a formula by using the available methods, it...
- It sounds like you want to paste the formula text?
If you have the formulatext function, you could use it to reference the cell containing the formula:
=FormulaText(A1)
Alternatively, you could try formatting the cell as text and the copy paste the formula from the formula bar to the destination cell.
If my understanding of what you're trying to do is correct, then the paste special-formula behavior is not a bug as it has always pasted in the formula as an expression to be evaluated.
Instead of copy/pasting into cells, you paste the contents of the formula bar, it is possible to transfer an exact copy of the formula from the source cell. If, in addition, the destination cell has a number format of 'text', that will block the evaluation so you will be left with a visible formula.
As for the problem of copied cells updating when pasted to a new location, my answer was to ditch the whole sorry mess that is relative referencing. If all referencing is done via defined names (these default to absolute references) the Excel 365 dynamic array formulas will spill to the correct size and so avoid the manual copy/down or copy/right that is so often the cause of user error.