In Episode #1705, Bill addressed the question of wrapping a group of existing Formulas with one New Formula with the use of VBA [Visual Basic for Applications]. However, today in Episode #1713, Bill shows us an interesting Formulaic solution to this question.
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1713 - Add a Function around all formulas in a Range!
We last talked about this in episode 1705 now, where I came up with a VBA way to solve this.
But JohnS sent in a interesting non-VBA way to solve it using the FORMULATEXT function.
So you have to be in Excel 2013, John suggested adding, you know, the opening part of the function, and then use FORMULATEXT of the original formula.
But we have to start at position 2 to get rid of the leading equal sign, and then just, you know, make it long enough to handle any formula, and then the closing part.
Now I'm going to take John's idea here, I'm going to make it a little bit better, possibly, or at least different.
Before the = I'll put a ^, and that's going to create something that will not be seen as a formula initially, and we will copy that throughout.
So the formulas will copy, and then paste those values, copy those values now, and paste on top of our formulas, and then a Ctrl+H to change every occurrence of ^= to =, which will convert all of those back to formulas.
How simple is that, oh, you see I've missed a column there, when we copied across.
Thanks to John for sending that idea in, brilliant use of FORMULATEXT in Excel 2013.
I want to thank everyone for stopping by, I'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1713 - Add a Function around all formulas in a Range!
We last talked about this in episode 1705 now, where I came up with a VBA way to solve this.
But JohnS sent in a interesting non-VBA way to solve it using the FORMULATEXT function.
So you have to be in Excel 2013, John suggested adding, you know, the opening part of the function, and then use FORMULATEXT of the original formula.
But we have to start at position 2 to get rid of the leading equal sign, and then just, you know, make it long enough to handle any formula, and then the closing part.
Now I'm going to take John's idea here, I'm going to make it a little bit better, possibly, or at least different.
Before the = I'll put a ^, and that's going to create something that will not be seen as a formula initially, and we will copy that throughout.
So the formulas will copy, and then paste those values, copy those values now, and paste on top of our formulas, and then a Ctrl+H to change every occurrence of ^= to =, which will convert all of those back to formulas.
How simple is that, oh, you see I've missed a column there, when we copied across.
Thanks to John for sending that idea in, brilliant use of FORMULATEXT in Excel 2013.
I want to thank everyone for stopping by, I'll see you next time for another netcast from MrExcel!
