It's like the Shell Game...but perhaps more intriguing. Using Paste Special in combination with an Operation [available in the Paste Special Dialogue] Bill is working between Excel and the Clipboard to make changes in his Worksheet. What Bill discovers is an odd disconnect between Excel and the Clipboard once the first operator and Paste operation is completed... Follow Bill in Episode #1582 as he describes and models this odd phenomenon and maybe you can keep it from affecting you!
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast Pace Surprise.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
i got a mystery I don't know if it's a mystery I got a puzzle, got a puzzle for you today.
I'm going to display the clipboard here so we can see what's on the clipboard.
So I have some numbers here, sixes and a formula that says we're going to take ten minus this minus one and that gives us 3.
i'm going to put that three on the clipboard CTRL C, so we now have the three on the clipboard, I’m going to come over here and I'm going to paste special, so I'll ES, paste special.
I'm going to paste the value so that formula currently is 3, I’m going to paste values and add it so we're taking a 3 adding it to the sixes clicking okay and we get 9 as expected, right no surprise there, ah but these nines and these sixes are averaged up here, see that's an average formula, and so now that cell that's on the clipboard it originally had a three but now it has a two.
The clipboard over here is showing that there's a three on the clipboard but what's really on the clipboard is it a three or is it two?
I figured once I hit CTRL C and copied that three to the clipboard it was going to stay on the clipboard and I could paste and paste and paste and paste and get threes the whole day long until I press escape but check this out so 7 + 3 should be 10 right, let's do a paste special and we'll do values add.
All right, so what do you think 7 + 3 is 10 or are we going to get 7 + 2 is 9?
Go ahead, cast your vote for us, either the two or three on your television remote control.
Tally those up, not really, okay, click OK.
Seven plus three is what I thought we would have got 7 plus a clip or three but no it's actually going back and re-evaluating that cell and seeing if that cell is currently a two and it added the two.
Shocked and amazed that that would happen.
Never run into that before, I had a situation where there was a formula here where I was adding three years to this date here, I copied and pasted to the whole column and then I did the same thing on another worksheet and it looked like the pace wasn't working it's like I somehow I did the clipboard but now because this now had a zero it was a live formula.
I was getting, I was adding zeros all the way along really, really surprising.
Shoot me a comment if you're watching at either Lipson or YouTube and let me know what did you think was it going to be a three or did you know it was going to be a two.
Well hey, I want to thank for stopping by see you next time for another netcast with MrExcel.
Learn Excel from MrExcel podcast Pace Surprise.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
i got a mystery I don't know if it's a mystery I got a puzzle, got a puzzle for you today.
I'm going to display the clipboard here so we can see what's on the clipboard.
So I have some numbers here, sixes and a formula that says we're going to take ten minus this minus one and that gives us 3.
i'm going to put that three on the clipboard CTRL C, so we now have the three on the clipboard, I’m going to come over here and I'm going to paste special, so I'll ES, paste special.
I'm going to paste the value so that formula currently is 3, I’m going to paste values and add it so we're taking a 3 adding it to the sixes clicking okay and we get 9 as expected, right no surprise there, ah but these nines and these sixes are averaged up here, see that's an average formula, and so now that cell that's on the clipboard it originally had a three but now it has a two.
The clipboard over here is showing that there's a three on the clipboard but what's really on the clipboard is it a three or is it two?
I figured once I hit CTRL C and copied that three to the clipboard it was going to stay on the clipboard and I could paste and paste and paste and paste and get threes the whole day long until I press escape but check this out so 7 + 3 should be 10 right, let's do a paste special and we'll do values add.
All right, so what do you think 7 + 3 is 10 or are we going to get 7 + 2 is 9?
Go ahead, cast your vote for us, either the two or three on your television remote control.
Tally those up, not really, okay, click OK.
Seven plus three is what I thought we would have got 7 plus a clip or three but no it's actually going back and re-evaluating that cell and seeing if that cell is currently a two and it added the two.
Shocked and amazed that that would happen.
Never run into that before, I had a situation where there was a formula here where I was adding three years to this date here, I copied and pasted to the whole column and then I did the same thing on another worksheet and it looked like the pace wasn't working it's like I somehow I did the clipboard but now because this now had a zero it was a live formula.
I was getting, I was adding zeros all the way along really, really surprising.
Shoot me a comment if you're watching at either Lipson or YouTube and let me know what did you think was it going to be a three or did you know it was going to be a two.
Well hey, I want to thank for stopping by see you next time for another netcast with MrExcel.
