See for yourself if you reproduce this. I am an advanced user but I spent substantial time constructing a noise-free and very explicit example so it is very very easy, and very quick, to follow the steps below and reproduce (or not) the bizarre result, though in my experience only about 3 people will do so even though the entire process takes maybe 90 seconds. For you 3, then:
XL2010 (you can try something newer too)
Ctrl-N for New WB with at least sheet1, sheet2 (add a sheet2 if you only have Sheet1 showing)
"Step A"
On sheet1 in B5 type =A5-A4 and hit enter
select B5
Ctrl-C
up arrow
shift up-arrow (B3:B4 is now selected)
Ctrl-V
"Step B"
Select B3
Ctrl-shift-down arrow
Ctrl-C
Ctrl-page down (activate sheet2)
select B1
Ctrl-V
First problem appears. Sheet2!B2 has an incorrect formula.
At this point apologists may say, well you made an invalid paste on the first line so "all bets are off." If that's an attempted explanation, try this exact same exercise in any previous version of Excel and explain that.
More observations:
"Step C" If you go to sheet1 and copy B5 and paste over sheet2!B1:B3 you get the CORRECT result.
"Step D" If you go back to sheet1 and copy B3 and paste over sheet2!B1:B3 you get two wrong results (sheet2!b2:b3). Does your Sheet2!B2 and B3 show #REF like mine does?
"Step E" On sheet1 paste B5 over B3, copy B3, and paste over sheet2!B1:B3 and now sheet2 is back to the correct result. But B3 was a copy of B5 to begin with. So why would copying B5 over B3 change anything?
"Step F" is the last one: if you now select B3:B5 on sheet1 and paste to Sheet2!B1 you now get the CORRECT answer!
Step D and Step E are the same, yet give different answers. All you did was add in Step E, copy sheet1 B5 over B3; but that is exactly what you did in Step A, making B3 a copy of B5. Brrr.
And Step F is practically the same as step B. Brr.
In case I merely have a site-specific issue, I thank anyone who confirms or denies the behavior on 2010 or 2013. If you do nothing but that, it will be helpful and appreciated.
Bookmarks