If you need to make an exact copy of formulas, you can use the method from episode 866 or this Fill Across Worksheets solution. Episode 867 shows you how.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
In yesterday's episode, we talked about a great tip from Marcus in Germany.
Now, Marcus said, "Hey, if you need to take these formulas, and make an identical copy of these formulas in another range or another worksheet or another anything..." he showed us how to use Edit Replace.
There's another way to do this, provided you're going to another spot in the same worksheet.
And let me show you how we're going to do this.
I'm going to insert a new worksheet: Insert; Worksheets-- Of course, that becomes Sheet 2; so I choose the original worksheet and then shift-click on the second worksheet.
That's going to put us in Group mode, and now I'm going to select my formulas in the original worksheet, and I'm going to use Edit; Fill; Across Worksheets.
And what that does for me, is that takes all of my formulas here on the original worksheet and puts them on the grouped worksheet.
So, we'll do N+Down to find those.
Okay, so there's my formulas.
Now I'm going to do a cut-- I want to Ctrl+X to cut, go next door, Control V to paste to Column D, and do the steps in Reverse so Sheet 2 is selected.
And then, we can either Shift+click or control-click on data, and I'll do Edit; Fill; Across Worksheets; click Go, and now when we go back to our data sheet, you'll see over there in Column D, we have identical formulas pointing to the original ranges.
And, of course, now we can edit this-- let's say if we needed to change it from SUMIF to a COUNTIF-- leave off that last bit and Ctrl+Enter-- straight down.
So, anytime you need to basically cut formulas and make a copy of the formula-- so instead of a cut and paste-- a cut and paste with a copy of the original spot, let's say.
Couple of ways to do either Marcus's method from yesterday, changing the equal to text, or this method using fill across worksheets both will work.
I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
[ music ]
In yesterday's episode, we talked about a great tip from Marcus in Germany.
Now, Marcus said, "Hey, if you need to take these formulas, and make an identical copy of these formulas in another range or another worksheet or another anything..." he showed us how to use Edit Replace.
There's another way to do this, provided you're going to another spot in the same worksheet.
And let me show you how we're going to do this.
I'm going to insert a new worksheet: Insert; Worksheets-- Of course, that becomes Sheet 2; so I choose the original worksheet and then shift-click on the second worksheet.
That's going to put us in Group mode, and now I'm going to select my formulas in the original worksheet, and I'm going to use Edit; Fill; Across Worksheets.
And what that does for me, is that takes all of my formulas here on the original worksheet and puts them on the grouped worksheet.
So, we'll do N+Down to find those.
Okay, so there's my formulas.
Now I'm going to do a cut-- I want to Ctrl+X to cut, go next door, Control V to paste to Column D, and do the steps in Reverse so Sheet 2 is selected.
And then, we can either Shift+click or control-click on data, and I'll do Edit; Fill; Across Worksheets; click Go, and now when we go back to our data sheet, you'll see over there in Column D, we have identical formulas pointing to the original ranges.
And, of course, now we can edit this-- let's say if we needed to change it from SUMIF to a COUNTIF-- leave off that last bit and Ctrl+Enter-- straight down.
So, anytime you need to basically cut formulas and make a copy of the formula-- so instead of a cut and paste-- a cut and paste with a copy of the original spot, let's say.
Couple of ways to do either Marcus's method from yesterday, changing the equal to text, or this method using fill across worksheets both will work.
I want to thank you for stopping by, we'll see you next time for another netcast for MrExcel.
[ music ]
