Markus from Germany sends in this tip. Say that you have to make an exact copy of a range of formulas. It is like you need to do a CUT and PASTE, but also leave the original cells where they were. Normally, a Copy and Paste will rewrite the formulas. Using this trick from Markus, you can achieve the desired result. Episode 866 shows you how.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, we have a great tip today from Marcus in Germany.
Now, the situation here is that we have a whole series of formulas and we need to take these formulas and replicate the formulas in another spot.
I don't want to Copy and Paste.
I don't want to Copy and Paste because I need the new formulas to point to the exact same cells as the original formulas, and whoever built the formulas did not put dollar signs, and I really don't want to go through and re-edit these formulas to put all the dollar signs in.
So, here's a cool trick that Marcus sent in.
We're going to choose all of our cells; we're going to do Edit; Replace: and we replace every occurrence of = with the word "equal"-- actually, you can put any word in you want; Replace All; click OK; click Close.
And we'll then Copy and Paste-- Copy and Paste.
Well, because this is just text, the Copy and Paste made an exact copy, including the original addresses.
Now, we use Edit; Replace, again; and change every occurrence of the word "equal" to the symbol =; Replace All; click OK; click Close; and we end up with identical copies of the original format.
This is kind of like doing a Cut and Paste, but keeping the original sales exactly where they were.
Want to thank Marcus for sending that in, we'll see you next time for another netcast from MrExcel.
[ music ]
Well, we have a great tip today from Marcus in Germany.
Now, the situation here is that we have a whole series of formulas and we need to take these formulas and replicate the formulas in another spot.
I don't want to Copy and Paste.
I don't want to Copy and Paste because I need the new formulas to point to the exact same cells as the original formulas, and whoever built the formulas did not put dollar signs, and I really don't want to go through and re-edit these formulas to put all the dollar signs in.
So, here's a cool trick that Marcus sent in.
We're going to choose all of our cells; we're going to do Edit; Replace: and we replace every occurrence of = with the word "equal"-- actually, you can put any word in you want; Replace All; click OK; click Close.
And we'll then Copy and Paste-- Copy and Paste.
Well, because this is just text, the Copy and Paste made an exact copy, including the original addresses.
Now, we use Edit; Replace, again; and change every occurrence of the word "equal" to the symbol =; Replace All; click OK; click Close; and we end up with identical copies of the original format.
This is kind of like doing a Cut and Paste, but keeping the original sales exactly where they were.
Want to thank Marcus for sending that in, we'll see you next time for another netcast from MrExcel.
[ music ]
