The Scenaro? Brendan created a cool Formula about six (6) months ago. He edited that Formula today and it won't work anymore. In Episode #1368, Bill shows you why and how to correct the issue.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1368: Edited the Formula, now it won’t work.
Hey, today's question sent in by Brendan.
Brendan has a spreadsheet and he actually wrote this formula a while ago and it says-- it's a nice formula we're not even going to talk about the formula, but Brendan went in and he needed it change this from “utilities” to “lines.” So he comes in here changes it and presses Enter.
The formula doesn't work.
It worked with “utilities.” All he did was change “utilities” to “lines.” He got everything right, right?
Brendan's like, “this is why people hate excel.
Write the formula and it works, edit the formula and the formula doesn't work anymore.” All right hey let's undo, Ctrl+Z and we're going to take a look at the formula up in the formula bar.
Anytime that you see a formula up in the formula bar that has curly braces around the whole thing; that is a sign that this formula is not a regular format but it is an array formula.
Now Microsoft calls them array formulas.
I don't call them array formulas because I can never remember how to enter array formulas.
I call them CSE formula.
CSE stands for Ctrl+Shift+Enter.
The other clue is, if it's a formula that makes your head spin then it’s a pretty good chance it's an array formula or a CSE formula.
So anytime that you want to edit an array formula, go ahead make the change but then don't press Enter or the down arrow or the right arrow hold down Ctrl and Shift, Ctrl and Shift and press Enter.
The thing will work, all right.
Most frustrating thing, I wish there was some way that they could say hey you're editing an array formula we're going to automatically put it in as an array formula or even think you just realize it's an array formula and take care of that because Ctrl+Shift+Enter is so arcane who the heck can remember that and even if you figure it out once, six months from now when you come back to the spreadsheet it’s like “it’s easy let’s change utilities into lines” but no deal So this is one of those where we're going to give Microsoft a great big fail for this one because it is just so hard.
Brendan, I’m with you on that one, very, very frustrating.
Well hey, I want to thank you for stopping by and we’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1368: Edited the Formula, now it won’t work.
Hey, today's question sent in by Brendan.
Brendan has a spreadsheet and he actually wrote this formula a while ago and it says-- it's a nice formula we're not even going to talk about the formula, but Brendan went in and he needed it change this from “utilities” to “lines.” So he comes in here changes it and presses Enter.
The formula doesn't work.
It worked with “utilities.” All he did was change “utilities” to “lines.” He got everything right, right?
Brendan's like, “this is why people hate excel.
Write the formula and it works, edit the formula and the formula doesn't work anymore.” All right hey let's undo, Ctrl+Z and we're going to take a look at the formula up in the formula bar.
Anytime that you see a formula up in the formula bar that has curly braces around the whole thing; that is a sign that this formula is not a regular format but it is an array formula.
Now Microsoft calls them array formulas.
I don't call them array formulas because I can never remember how to enter array formulas.
I call them CSE formula.
CSE stands for Ctrl+Shift+Enter.
The other clue is, if it's a formula that makes your head spin then it’s a pretty good chance it's an array formula or a CSE formula.
So anytime that you want to edit an array formula, go ahead make the change but then don't press Enter or the down arrow or the right arrow hold down Ctrl and Shift, Ctrl and Shift and press Enter.
The thing will work, all right.
Most frustrating thing, I wish there was some way that they could say hey you're editing an array formula we're going to automatically put it in as an array formula or even think you just realize it's an array formula and take care of that because Ctrl+Shift+Enter is so arcane who the heck can remember that and even if you figure it out once, six months from now when you come back to the spreadsheet it’s like “it’s easy let’s change utilities into lines” but no deal So this is one of those where we're going to give Microsoft a great big fail for this one because it is just so hard.
Brendan, I’m with you on that one, very, very frustrating.
Well hey, I want to thank you for stopping by and we’ll see you next time for another netcast from MrExcel.
