To writing lambdas so in case you're wondering what lambdas are i have different videos on this i'm going to link them below and in the cards as well so let's take a first look at the.
Advanced editor and see how we could potentially use it as a replacement for name manager i've opened up a blank workbook to grab the add-in go to insert get add-ins.
Now just type in advanced formula and you should see pop up here it's the advanced formula environment click on add and then continue once it's added it's going to come to.
The home tab right at the end the advanced formula environment click on it and you're going to get a new pane opened up on the side it tells us that named formulas in the workbook.
Message that i need to save the names and then try writing if blank in a cell okay so let's do this together one function that it added for me just for me to be able to test is the if blank.
Function now we can see that this is based on the lambda function now these might look a bit complex but they're actually quite simple so we're going to create a lambda together as well later.
On and add it to name manager but for now we're just going to use the one that we have but if i want to test it out on the side okay so i'm going to type in something.
Here and let's have a blank and then have it be here if i want to go ahead and try it out right here it's not going to show remember there was a message that i got.
It was pointing to this that i need to send this to name manager so whatever you create here doesn't automatically land in name manager you have to sync it by clicking on this and they're going to.
Posts Related:
Land in name manager so if i go to
Formulas name manager i can see them right here and when they're in name manager it means that i can use them if blank pops up here.The value is this and if it's blank let's just put in missing in quotation marks right so this is similar to the if error function except we've created our own if blank.
Function a great thing about this environment is that it's so much more flexible as compared with name manager check this out if i go and edit this.
Now right here there's the if function right when i open the bracket i get help i know what argument comes first versus the condition then it's when true and then when false i don't get this when.
I'm in name manager i get the ability to rename right here i can delete or i can share now currently sharing is limited to importing from text you can basically copy this to your clipboard open up your.
Other excel files and then paste this in okay so this is the manager view this is like a card view of your names in name manager now you can view everything in the editor view as well this is better.
For your longer functions or if you're working on multiple different names at the same time because you see everything in one place you also have the ability to just add enter here and break up your.
Functions in an easier to read way when you reach the end of your name you put a semicolon and then you move on to the next name and so on okay so now let's open up a workbook that already has.
Names in it so i've created these some time ago and i want to see what happens when i have this add-in installed okay so here over in the file i have two separate tables the first table is.
Called q1 table and the second one is q2
Table i already have some named ranges in place so if you go to name manager i have this name get total so these ones.With the blue icons these are named ranges and the ones with the gray header are my table names so i want to figure out what get total does.
I notice there's the choose function here and we have a reference to the report page cell b3 if i want to see what's in that page and i click on report notice what happens it.
Adds it here to my formula i don't want that now if i want to use the arrow keys to move around again it's going to add this reference which i don't want so i have to remember to press the f2 key.
Before i move around here i also don't get help from excel as i'm writing the function so if i forget how to choose function works i have to write it in an excel cell and then copy and.
Paste it in here so it's quite a headache to work in this view i'm not going to save anything let's just leave this and see how everything looks with the new advanced formula editor.
Environment let's go to home and click on this we get our names pop up here in a very nice and readable way we don't get the table names but we get our named ranges.
So here if i want to figure out what's in the report page cell b3 i can move around and i'm going to keep my formula in view i can see all my names and what's inside.
The names right here so this is q1 revenue that references the revenue column in my q1 table this makes it so much easier to read and understand what is behind these names now how about.
Creating a name from scratch so let's go to this page and let's create one together so a function i use often is the filter function and i use it to exclude blanks so let's say i want.
This list but i don't want those blanks in between i'm going to reference this range then i'm going to include the cells that don't equal to nothing and if my list is.
Empty i want to return nothing okay so i'm going to close the bracket press enter and i get my range excluding the blank rows now because i use this often it.
Would be nice to create my own customize function for this i can do that using a lambda we can set it up from scratch in here it doesn't matter if you want to set it.
Up in the manager view or the editor view just go with whatever you feel more comfortable with i'm going to use the manager view in this case click on the plus give my formula name let's say i.
Want to call it no blank and i'm going to start off here with the lambda function now in the lambda function we need to define our parameters and then our.
Calculation so if we take a look at this the parameter is basically wherever you have a cell reference or a range reference instead of referencing this range we're going to give this a name.
And you can give it any name you want i'm just going to call it my range now i just have one parameter because i'm referencing the same range so then comes my calculation and my calculation is.
Something i've already figured out it basically uses the filter function the first thing i need is the array now i already have a name for it it's called myrange and notice as i'm typing it in.
We can see it pop up here intellisense is at work i just press tab to select it next i want to include it we're referencing the same range so we're going to go with my range and then go.
With this not equal to nothing and the last argument if empty i'm going to use nothing now that this is in place click on add and we have it added down here if i switch to editor view.
I can see it all currently in one line but it's very easy to break it up and put it on multiple lines so if you want you can already write your formula in this view and break it up as you need to.
You can also add comments to this so if i use slash and the asterisk sign notice everything is commented out so to end my comment i'm going to put the asterisks and slash and now inside here.
I can type in whatever i want i'm going to put exclude blank cells okay so now that we have our customized function added here we need to send it to name manager to be.
Able to use it because currently if i go and type in no blank it doesn't show up it's only going to show up after i sync it once i click on this what it does is that it automatically adds it here and.
When i save my workbook it's going to be there and i can use it type in no blank select the range close bracket and i have my function that i can use to wrap my workbook i can also embed it.
Inside other functions like for example the unique function another great feature is this one right here we get the ability to add a github gist url and import a bunch of functions.
That were already created and add them to our workbook so chris gross from microsoft already shared with us a link to his github gist which we can test right here so in the blog post that.
Announced that lambdas were released to production and that we have this great advanced formula environment if you scroll down we get to see a bunch of really neat examples and we get this url.
To the github gist so when i click on it it opens it up all we need is this actual url so i'm going to copy it and then let's go back to excel import and paste it in.
