Gary sent in a better way to build MegaFormulas. Gary must be catching up on old podcasts, because he sent a note about Episode 387. Compare my way and Garys way of building MegaFormulas in Episode 525.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, we have a comment sent in by Gary Whiteford.
Gary, he must be catching up on old podcast, because he just viewed episode number 387 and said hey, there's a better way to do that.
Now, that was from last November about 150 episodes ago.
So, if you don't remember, I was talking about how to build a MegaFormula.
In yesterday's podcast, I had to take three columns to solve the problem sent in.
Well, what if I want to do that in just one column?
Now, combine all those formulas into one column.
My tip for combining the formulas involves these steps.
Our N formula refers to cell C1. So, I'll go back to cell C1, hit the F2 key, to put it in edit mode.
And then copy everything except for the equal sign.
Use Ctrl+C to copy and escape to get out of edit mode, and then go back to where we have C1 in the original formula and use Ctrl+V to paste.
This can get rather tedious and Gary said.
Hey, there's a better way to do that. In this case we have several instances of cell B1 in the formula.
Let's go to B1 and use the exact same method you used before. Use F2 to go into edit mode.
Highlight all of the characters except for the equal sign and copy.
But now you select a couple of cells.
You have to select a couple of cells and we'll use edit replace.
The fast way to use edit replace is Ctrl+H.
Find what. We will say that we want to find every occurrence of B1 replace with, I'll use Ctrl+V to paste.
And do a Replace All.
And, very quickly, you'll see that, Excel has now rewritten our formula, anywhere that it had referred to B1 before.
It's now pasted in the value of B1.
Now, if we had any other terms in this case. I'm more good.
We're back to A1. If we had any other terms, we could go through and use those steps again.
So, there you have it. Two different ways to create a MegaFormula.
My method from 387, where we'd actually would go in and use copy and then Ctrl+V to paste it into the formula, or Gary's method where again, he uses copy to get a piece of the original cell, goes back and uses edit replace.
Now Gary pointed out you, have to make sure that you select more than one cell when you do the edit replace.
That's why we selected cell C1 and D1.
If you had selected only one cell and did the edit replace, excel would work on the entire worksheet, which is not what we want.
Gary's other point is, you have to make sure that B1 is unique.
If we had had a reference to both B1 and B11, that would be a problem.
You would have to correct cell B11 first and then B1. Otherwise, the replace of B1 would unfortunately get those B11 references as well.
Thanks to Gary for sending that in.
I'm sending out an excel master pin, to thank him for giving us a great new tip.
If you have a tip or a question for the podcast, please feel free to drop us an email or leave a voicemail and we'll get to you on a future podcast Thanks for stopping by. Will see you next time for another netcast from MrExcel.
Today, we have a comment sent in by Gary Whiteford.
Gary, he must be catching up on old podcast, because he just viewed episode number 387 and said hey, there's a better way to do that.
Now, that was from last November about 150 episodes ago.
So, if you don't remember, I was talking about how to build a MegaFormula.
In yesterday's podcast, I had to take three columns to solve the problem sent in.
Well, what if I want to do that in just one column?
Now, combine all those formulas into one column.
My tip for combining the formulas involves these steps.
Our N formula refers to cell C1. So, I'll go back to cell C1, hit the F2 key, to put it in edit mode.
And then copy everything except for the equal sign.
Use Ctrl+C to copy and escape to get out of edit mode, and then go back to where we have C1 in the original formula and use Ctrl+V to paste.
This can get rather tedious and Gary said.
Hey, there's a better way to do that. In this case we have several instances of cell B1 in the formula.
Let's go to B1 and use the exact same method you used before. Use F2 to go into edit mode.
Highlight all of the characters except for the equal sign and copy.
But now you select a couple of cells.
You have to select a couple of cells and we'll use edit replace.
The fast way to use edit replace is Ctrl+H.
Find what. We will say that we want to find every occurrence of B1 replace with, I'll use Ctrl+V to paste.
And do a Replace All.
And, very quickly, you'll see that, Excel has now rewritten our formula, anywhere that it had referred to B1 before.
It's now pasted in the value of B1.
Now, if we had any other terms in this case. I'm more good.
We're back to A1. If we had any other terms, we could go through and use those steps again.
So, there you have it. Two different ways to create a MegaFormula.
My method from 387, where we'd actually would go in and use copy and then Ctrl+V to paste it into the formula, or Gary's method where again, he uses copy to get a piece of the original cell, goes back and uses edit replace.
Now Gary pointed out you, have to make sure that you select more than one cell when you do the edit replace.
That's why we selected cell C1 and D1.
If you had selected only one cell and did the edit replace, excel would work on the entire worksheet, which is not what we want.
Gary's other point is, you have to make sure that B1 is unique.
If we had had a reference to both B1 and B11, that would be a problem.
You would have to correct cell B11 first and then B1. Otherwise, the replace of B1 would unfortunately get those B11 references as well.
Thanks to Gary for sending that in.
I'm sending out an excel master pin, to thank him for giving us a great new tip.
If you have a tip or a question for the podcast, please feel free to drop us an email or leave a voicemail and we'll get to you on a future podcast Thanks for stopping by. Will see you next time for another netcast from MrExcel.
