Rebecca has attended:
Excel Introduction course
Excel Intermediate course
Copying cells that contain a formula
Hi,
I am trying to copy and paste a row of data. The cells contain a sum formula.
When I try and paste the cells in another (empty) sheet the error REF! pops up in all the cells.
How do I paste this successfully.
Thanks
RE: Copying cells that contain a formula
Hi Rebecca
Thank you for using the forum to ask a question.
This is a relevant question for any formula you paste between sheets.
On the original sheet where the data is, the SUM function is "looking" at a range of cells to add up on that sheet
e.g. =SUM(A5:E5) with it adding up into the cell F5
This is looking at the 5 cells to the left of F5
If you were to copy this on to a new blank sheet as long as you pasted it in a cell where it had 5 cells to the left it would work. If not you get a #REF error as the cell reference can't be found.
If you want to copy just the data then use paste special options - right click and choose 123
Or
If you want to keep the numbers linked so if one sheet updates they update use paste special and choose the link/chain symbol
Let us know if that explains and solves your question
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
