I think if Paste Values pastes something that is no longer on the screen that is weird. How long do you wait to clear the clipboard if that's the case? What if I copy some cells, then change everything about my workbook (changed sheet names, changed named range names, refreshed linked data, inserted rows in the middle of my copied range...), then paste, do you still expect it to paste the original values? Do you expect that most users understand what's about to happen?
I think the whole question is avoided by clearing the clipboard earlier than might sometimes be desirable.
Paste Values should paste the values that it had at the time of copying, period. Other programs behave thusly. If I'm making an image in Inkscape, I copy a portion, delete it and then paste it, I get the value I copied even though it had since been deleted. Same as if I edited it after copying, like changing a color. Excel should mimic that.
So if the underlying value changes enough that pasting anything but the values doesn't make sense then what's the UI here? Ctrl+C does nothing but Ctrl+Shift+C gives you the option to paste values with the inappropriate ones greyed out? Does the dotted border on the copied text go away because ctrl+C isn't going to work or does it stay because some portions of paste special can still work?
I think the scenario for pasting formula is the same as for pasting values: paste what was there at the time of copying. Is there a situation where this doesn't make sense? I'd be fine with it being kinda-wrong in situations like the original cells referred to another cell but now there's been a new row inserted and those cells are elsewhere and the clipboard hasn't updated. That's what I would expect a copy to do. Excel allows references to cells in other spreadsheets and if you edit the other spreadsheet, then the references in your first spreadsheet don't update to reflect that, so the clipboard would just act like another spreadsheet.
Edit: the better way to say this is that "copy (do something) paste" should always act the same as "copy, paste on a new blank spreadsheet, (do something) and then copy and paste that onto the original spreadsheet".
I'm not sure I am; the current behavior is almost always surprising and unexpected. You effectively need to keep a list in your head of everything that will clear your clipboard, which is really a list of everything that could ever cause problems, more often than not a flag that is entirely unrelated to your actual intent/action.
You might as well have it clear the clipboard if your next action isn't immediately paste and be done with it. Just don't bother to set the expectation that pasting will work like elsewhere.
The dotted border is kind of the problem. It represents an active connection between the current spreadsheet and what was copied, and that connection shouldn’t exist.
When implemented normally, ‘copy’ puts a copy of the selection on the clipboard; ‘paste’ takes whatever was on the clipboard and puts it at the current location.
Once you copy, it shouldn’t matter what you do on the spreadsheet, because you’re pasting from the clipboard.
Normal behaviour can be emulated by copy+pasting into a blank spreadsheet when you want to copy, and then copy+pasting from there when you want to paste.
But then you’ve just turned that spreadsheet into what the clipboard is supposed to be.
