Forum Discussion
#REF! When referencing cells that constantly change
I have Sheet2 cells referencing Sheet1 cells with a simple =Sheet1!B1 formula, but when the cells of Sheet1 are cut/pasted or copy/pasted over, I get #REF! errors. On some instances, I don' get a #REF! error, but the formula changes (example a cell that is supposed to be =Sheet1!B1 turns to =Sheet1!B6 because of the copy/past that occurred in Sheet1. Sheet2 is intended to mirror the cells of Sheet1, which acts as a schedule (which is always changing). How can I fix or prevent #REF! and formula changes from happening in Sheet2? I've tried the Find/Replace All (leaving the Replace blank) method but unsuccessful. If you can show me on the worksheet attached, I would be most appreciative.
Sheet1Sheet2
You don't say WHAT you are pasting, what FORM it takes in its origin, and where that ORIGIN is.
You do mention bringing it in as COPY-paste or CUT-paste (that distinction can make a difference sometimes)
What you don't specify, and where I'd go first, is to the matter of how you implement the "paste."
I wonder if you might not be aware of the Paste Special option. In particular, the possibility of selecting "Paste...Values..." And I can't show you that on your attached sheet, because it doesn't include whatever it is that you are pasting that causes the problems in the first place.
Try that and see if it (or one of the other options available there) might be what you need.
- Thanks for the reply. in this example, C7:10 was cut and pasted into G1:4 in sheet one. It seems like the solution is using the paste special-values , but there's extra steps needed like formatting the color and deleting the copied cells.
in this example, C7:10 was cut and pasted into G1:4 in sheet one
C7:10 from sheet 2??!! What is supposed to happen with the extra rows intervening in sheet 2? That doesn't seem to make sense to me?
Could you back up a bit and give the full context here, keeping confidential anything that might be confidential, but what is the full process? Are other data sources involved? What ultimately is this about?
