Save Self Formula with airSlate SignNow

Save self formula

alright let's go ahead and get started welcome everybody to the intermediate formula training my name is Lance Merkin and I'm a customer success manager at smartsheet so my job is to make sure that everybody is getting the most out of the platform from the dashboards to the reports all the way down to the sheets which is what we'll be focusing on today so one of the best ways to get the most value out of the sheets is the use of formula that's the best way to leverage it and I love formulas because there's always a reward no matter how easy or challenging a formula is there's always a reward and save time efficiency just overall productivity and so my goal today is to within this hour show you some of the most common use cases we get and and show you how to use those kind of building blocks to see if you can increase the way you're building the formulas as well so with that said here's a little disclaimer I might say something that's forward thinking I don't know what but I might say it here so what I want to do is we're gonna break this into essentially half this session the first half is I'm going to talk about nested if statements the ability to have a cell make decisions for you based on what you tell it to do and we're gonna go into some deep dive on that and then we'll stop a little bit for some questions and then the second half I'll resume and talk about some cross sheet formulas those are the functions that can grab data from other sheets and pull it into one sheet for us and that's useful for a lot of scenarios especially like if you're doing any kind of dashboarding and you want to have a one sheet that has all the information for you and then you can take that information just point to that one sheet and bring it in and turn it into graphs and things of that nature so with that said let me get get started on this here and I want to see if you guys are familiar with this this request that comes in a lot to us so what this is is I simplified a sheet right here but I this happens to us in customers tickets us all the time where someone calls in and they'll ask if we can make these Harvey balls right here in this column change colors for us based on certain conditions they want certain things to be on their radar for instance in this case I have the customer said we define what the business rules were the business rules were anything seven days out they don't have to worry about it doesn't need to be on their radar that can be green but where it starts getting a little uncomfortable is from the three to seven day mark right in there they need these things to automatically turn yellow based on this due date right here and if it's within three days all the way to the past they need it to be red that's a red alert now all of this is a moot point if someone's coming up here and checking complete then it's great it doesn't need to be on anybody's radar it's been completed no need to be applying these rules but you can see it's dynamic so if I change like this date of ten fifth and put it out a little bit further to the eleventh that's a green or I can put it within that three-day period that's a that's turning it red at any time I can check this or uncheck it and it's gonna go gray and of course today or anything in the past should be red if it's still not complete so how did I get there so the first thing I want to jump into is just a little bit of theory of basic if statements and I'm just curious how many of you worked with just basic if statements before okay how about nested ifs all right so what just just for those of you that don't know so a basic if statement or an if function is composed of three parts we start with the word the function name if we open the parentheses and we perform some logical test and then we decide on that logical test it's a yes or no true or false type of test and if that's the case and it passes the second part separated by this comma is what do we do if it's true and then the third part is what do we do if it's false simple enough so in this example I'm pointing at some cell on row 101 and saying is it bigger than six and if it is greater than six I want to return the text value it's bigger than six and if not I want to say not bigger six now a nested if statement is just making this a lot more workable in the real world it's not common that we're just doing just one simple yes-or-no test we do need to do a few of these and so the concept of a nested-if is just a bunch of if statements together so we're I'm doing here is I'm taking that if statement and I'm putting it into the area of what to do if it's false in other words we test something if it's true we do something and if it's false we go to another if statement and we keep doing that over and over so let me show you what that looks like if I do this here's a nested if I even color-coded to make it look some way pretty but it's it's still a cluster in there but you can sort of read it like this it's yeah if some number is one return the text value one we can test that same cell again four to return the text value two and so forth and so on but this is one one big tip here for those of you that are building nested if statements whether you're building them or or you're deciphering someone else's nested if statement the best thing you could do is take this whole chunk right here and put it into some text editor some word pad something and just hit enter before every if right here so that it looks like this much easier to read this way now I can sort of tell what's going on here so now I can say oh yeah okay it's one two three four and five I get it and then if it doesn't say it fails all that then we'll use the word big now in this example right here there's a couple of times where the order of these makes a difference right now in this specific example it doesn't matter if I first test for three then for two and then for one it's not a big deal it does make a big difference if I'm doing greater than type of things and you're gonna see that in a second but right here it's not it's not a big deal it's important to know that when we do this nested if it goes to the very first one test it if it's true great it stops there's no more evaluating the rest of the statement it gets kicked out and we call that the order of operations and that plays a serious role when you're trying to figure out what order do I put all these if statements in and we'll get into some Strad for that but a better way to look at it is this is an order of operations kind of chart right here so we're doing some tests and if it's true we do something and if it's not we continue down it can be a completely different test we can test something - if that's true we are completely kicked out it's the first true that happens anywhere down the line we're stopped evaluating the statement so that's going to play an import role when we play when we build a logic for this let me go back over to this sheet so I'm gonna go to the next sheet over the next sheet I'm gonna show you is exactly the same columns but what I'm gonna do is I'm gonna break every business rule out so we can build an individual if statement for each one of these conditions that looks like this here's what I was told seven days out is green I want to make an if statement that just works for that one thing the second one is three to six days out is yellow I want to make a specific if statement just for that unless you're a wizard this pays off because if you're doing it one step at a time you can test each one of the steps before you put it all together and throw it in because if one thing if you just do it all at once and you and you put it into an individual cell and you get some kind of an error it's really hard to figure out where that's coming from so it's nice to make sure our each one of these working before I put it all together so I'm gonna go ahead and do that right here the seven days out I'm gonna introduce for those of you that don't know another function called the today function which is just returning today's value looks like this I was just to put it right here type in equals today with my opening close right there I'm gonna get today's value if I was to save this and look at this tomorrow this is going to be October fourth for tomorrow now I can adjust this clock to it could I could do something like this I could put right in the parenthesis if I want to adjust today's date and put in plus seven days of that then that's the tenth tomorrow that'll look like the eleventh and so this is in our very first statement if you look at this I'm testing is the due date over on cell 2 or wrote to over there is it greater than today's value plus up and if it is I want a green if it isn't I just typed the word nope you don't have to do that but I do it because it's a layman term and I'm able to tell if it's something I did or if it's something that smartsheet is generating back to me so now I can test this this is simple enough it's either if it's greater than today a plus seven great if it's today and the fifth it'll say nope all right the first so that's working for me fair enough I got seven days out is green second thing is now I got to do this one that's three to six days out this is probably the hardest of the if statements that I'm about to show you but because I got a test for two things in here and this one I got a test is it greater than the today's value plus three and less than today's value plus six to get that range so this is what this looks like and what I'm doing what you'll notice in here is I'm testing two things so I have to use an and statement an and statement or an and function all that does just so you can see it I'm gonna highlight this an and function is this piece right here that goes into the test area is I can put a bunch of tests that are going on I can put a bunch of different tests separated by a comma and if every single one of those passes then the entire and function returns are true and then I can move on as Sable the minutes yellow otherwise no an or function is very similar to that except at an or function you can put all these arguments in there separated by a comma and if any one of those is true then the whole or returns a true so this is a simple so in this and function I'm saying is the due date greater than today plus three and the due date less than today plus seven if both of those conditions are true then we'll go to yellow otherwise we go to no so that's the second one then the last one here or this second last one is 0-2 three days out or in the past so if I really think about this just logically really it's three days out from today all the way into the past so really I'm looking for three days from today is it less than that value so that's what this one is right over here you can see and I can test these because sometimes it's hard with logic using dates that calendar piece is kind of missing from my brain for that so I have to do a lot of these testing type of things to make sure it's working so if I go into the future it's gonna say nope but if it's something three days out or I can test it on the first yeah that should be lighting up red so that's that's working and then the last thing I was told and oh by the way this is a moot point is if it's complete don't worry about all of this so on this one I'm gonna show you what this one is if I could can check it or uncheck it that's all it's doing on the left side and if I look at the formula I put in there I basically said is the complete on row five over there that cell if it's one which is checked give me a gray and if it's not checked a zero then nope so this is these are the four things that I need to make this work where I can put these all together now I have something that's workable but this is where I have to remember my order of operations this is where I have to ask myself what order should I put this in because if it's in this order right here and something evaluates to green it's not going to go all the way down to see if it was checked or not so a good tip is to start with the thing that's most common and put that all the way at the top and you can just logically think it out to yourself if you were training someone to do this or something like that it makes a lot more sense if I move the complete is checked or not because everything else is a moot point after that so we should be doing this first let's check if it's that this box is checked or not and then we can go down the green yellow red now I'm gonna go ahead and put these together in a text editor do a little massaging around on this and I'll put it in and we'll make a working formula out of this so what I'm going to do is I'm going to take this very first one that we just did take it right here copy that throw it into my text editor do the next one down grab that throw it into the text editor next one down here and I'll have all my items in a row right here with this last one and I'm gonna go ahead and put that there so there's my four if statements now I'm gonna when I jumble this all together I needed them all look at one cell so what I'm gonna do is I'm gonna change this complete right here up here that's looking at Row two I'll change that to one I'll change this piece right here to one I'll change this to one because I just want to put on the first row when I put this all in with my text editor and then this last one right here will change that to one good enough now I don't need this is all gonna be one so I don't need all these equal signs so let me get rid of this the only place it needs to have an equal sign is the very first one and then to turn this into something that's usable what I'm gonna do is I'll get rid of the nope section here because what I'm basically saying is if the complete equals one if it's check to go to gray if it isn't go to the next if so I'm just gonna hit delete on these last ones this one this one this one and then because I'm using four ifs I'll add those last parentheses right here that's it I can now take this whole thing right here and put it into a cell and in theory it should work let's give it a shot so I'll go back over here I'll go back it over to this one and I'll throw it into one and now I can test it yeah that's doing what it's supposed to do if I go a bunch of days out yeah that looks right if I'm within the the three to seven day window this should be yellow anything in the past or maybe even tomorrow that would shine up a red and in the past so this is working exactly as I want it to and then I can autofill it down and by the way if I have if I have these form was going I always like to lock the column so that editors can't come in here and change it because if they were to change the value of that Harvey ball it'll it'll blow out the formula so it's a nice little thing to remember now going back to the nested if we got that working that that's how that's the methodology I use whenever I'm building something somewhat sophisticated when it comes to a formula for extra credit though we can clean this formula up a little bit more if we revert back to the idea that the order of operations I can rethink this out just a little bit more because now if I really think about it if we go down to this three to six days right here what I was doing is an and statement greater than three less than six but wait a minute if if it goes past the six days well this the one above it is going to get caught with the seven days so I really don't need that and statement I can just say is this greater than three days because of its past seven and more it's automatically gonna get turned green before it even gets here so I can say so I can revise this a little bit more and say you know what I'll just check if it's great in threes and finally if it's either if it's not checked if it's green or if it's yellow if it's neither of those if all of those don't happen it's got to be red why should we even have any logic to check if it's red so the exact even though we have a working formula if you want it to be a little bit cleaner you can rethink that logic I'll copy paste this exact thing right here so you can see them side-by-side but this is what I called a cleaner version of the formula if I put it right down here it's this one so it's a significantly smaller formula and it still works the same because what I'm saying is if the box is checked we want a gray if the due date is greater than today plus seven give us a green if the due date is greater than today plus three a yellow and anything else should be a red so a pretty straightforward there so do you guys have any questions about that nested if before we go into these cross sheet formulas yes yeah you could definitely you could make it equal to that date specifically that day you could set up or you could write a formula it would be exactly on that date so long as you look at it on that day so I'm hearing the question right you're asking can you set it to a specific date not more not less and essentially I'd be doing an equal to a specific day is that is that what it sounds like yeah yeah cuz you you could and if you want I can show you off to the side but yeah you can actually set it to where it's very specific like that and and build that out absolutely oh yeah let me show you so I put this I did put this into oh sorry the question was is I put this into the first cell now now can i replicate this all the way down to the other cells just autofill it down so let me show you let me show you that over here if I go back over here I had that working piece right here so now to bring it all the way down I can move my mouse all the way to the right here where it turns into a crosshair like this which turns it into an auto-fill mode and then once it's in a cross hair like this and this lower right of the square I can drag it all the way down now it works for all of these right here so you can see any one of these now I can see it changed it it changed it to row 3 or down all the way here you can see it's chained to row 8 so you can taught Oh fill it all the way down and and the other thing to note is as new entries come in brand new records come in what happens it'll automatically populate the cell that has the formula down if the two rows above it have a formula that's just how smartsheet works which is really nice as we're adding new records it's automatically bringing it down so we don't have to constantly babysit and and pull that formula down yeah like we do get we have a sheet that we keep internally we share with others that are some of the more advanced form is that we get but examples with Harvey balls specifically some people want to have they're using hierarchy and so they want the parent rows to be the weighted average of the children so if anyone or or they'll say or what I call the the worst the bad apple like if all the children are green the parent would be green but if any one of those children is red the parent needs to be red so there's different variations of of this and we a lot of times we've already done this we get this request so often that feel free to reach out to us because we'll usually have it at the side oh that's the one you want we get that so much that we can share that formula with you rather than having you reinvent the wheel what if I don't want it to count weekends there's a way that we can do net days it's another function in there that we can get into and so there's a at the end of this I'm going to leave you with a sheet that shows all the functions that we have and you can see how you can all the other things that you can do to embed into if statements and use interactively with examples to go with and so you yes there is a way to exclude or include weekends there are no so if you're bringing in an excel sheet the basic a lot of the the basic ones but there's some analytical type of functions and formulas that are only available in excel we also have hierarchy functions that are only available in smartsheet most of it is coming across the import duel is designed to get you started not necessarily you know replicate exactly what you're doing yeah in general if it's a big sheet the less functions that are being used the better the performance number one it's easier to use you just having less evaluations because what happens in smartsheet is when you open up a page all that data comes across then your computer gets to do all the formulas and then whenever changes are everything it brings it all back into smartsheet so the less that you're processing the better especially when it gets to be a big sheet hi in the event that you have to copy and paste data instead of having a new line inserted every time is there a way to make the formula copy to the bottom every time yes there's a way there's a series of shortcut keys that you can do kind of like an auto fill all the way down to thousand rows I don't have it memorized I've done it before but you basically do something where you're doing control down arrow shift and then ctrl D to autofill straight down something along those lines it's doable I have to look up the exact keys to do that and I'll take one more question then we'll kick it into the cross sheets for your due day it turns red when the day hits 12:00 a.m. in the morning is there a way to make it the end of day so if you were gonna do that right now I would adjust the day if you needed it to be the end of day maybe you could do it do one day more like eight days or abouts greater than six or equal to six we're coming out with oh this is a forward-looking statement we're coming out with we're coming out with with a time column and how that plays into that that might play into this stuff a little bit better than just using pure days so anyway I'll go ahead and and jump right now into the craw sheet formulas so as I mentioned Cross sheet formulas is a way it's just there's certain functions in I use them interchangeably functions formulas but there's certain functions that you can use that can not only calculate within the sheet itself but it can actually go out to another another sheet and pull those values in for you and so I'm gonna go through some of those real common ones this is the first case of an example so this what we're looking at right here is a simple time sheet in this company there's four employees and they do billable time in both Texas and Washington and so we're using a form to capture data the employees enter their time and every two time entry is coming into this sheet so on this sheet I can see the employees name right here and I can see the hours worked right here so what I'd love to do is be able to pull in I have an employee rate table over here that shows the employees and what their rates are so what I'd like to do is just pull in the rate right into here and just go straight down with that that would help me when I do my payroll I'll be able to know what I what I need to be paying everybody so where I'm going with this is this is a vlookup function that's gonna do that and this is one of the most time-saving functions I think ever invented because on a huge sheet I remember stumbling across this for the first time I was like oh my god that's been a ton of time but anyway imagine we're gonna do it to a few rows but you're gonna see how this a lot of you already know the vlookup but you'll see how how huge of a time-saver is this let me go ahead and type it in here start with the equal sign vlookup and I'm going to open parenthesis so it starts it put me on yellow the search value what's the what are we looking for when I'm at trying to match two as this employees name so I'm gonna click on that and then I'll go hit the comma to go to the second yellow right here now it's asking for the lookup table which is that other sheet so this is a cross sheet formula because I can push this blue reference another sheet and when I do that I'm going to go ahead and click on this and I'm going to go ahead and go find the employee rates table right here and what I need to do is I'm just gonna click on this column I'm not I don't want to click on this range of cells for my range because if I add more data I'll have to come back in here adjust it so what I'm gonna do is I'm going to hold my shift and do all four columns right here and I'm gonna call this something that I can remember so I can reuse it something like the employee range and I'm gonna go ahead and insert the reference right here now interestingly I don't know why sometimes it's done it's on certain functions but it brings the cursor right back inside the employee range in that bracket I want to move my arrow to the right so I can push comma again and now the next part the third part is asking what column do we want to bring back if we find that match and we want that fourth column so I'll put the number four here and then finally I'm going to do a comma now this is an important one and it's odd because if I don't if I don't go to this fourth part it will automatically try and look for a close match and I know only have two or three examples that ever need a close match what we most the time want is an exact match or no match I don't want close and also if we were to also do it it need to be sorted in order so I always remember to say no close match I want I want to type the word false which means exact match so now if I hit enter on this John has a rate of $100 an hour and I know that this is coming from somewhere because it has that blue inbound arrow so this is coming from somewhere so let me go ahead and do that same autofill I'm gonna go ahead and put the little cursor hairs on the lower right and then I'm just gonna drag down and let go and it'll fill all these in and these are live so if the rate for John changes it will update on this sheet for you so that's a that's a first example of a of a B lookup I have a quick question here it has that range that you give it the first value in the range so the question is does it have to be in that order the first value that it needs to be looking for has to be the first value in the range there's another way there's a if you need to find if you need to find a certain column but returns some other column back and it there's differences that way in the advanced class tomorrow they're gonna be talking about index matching which is where you're combining two formulas to do that so here we are we have this so we have this rate and this is great this is pulling in the rates that I want everything's wonderful now now I'm ready to do some payroll on this and so what I'd like to do is just have this one sheet that's always fresh and updated whenever I come into it I'll start with something simple it'd be nice to know right off the bat from that other employee that transaction log how many times John is showing up so if I want to do that all I have to do is I can just type in equals account if this is not to be confused with a a if statement anyway it's a very different function so account if just really needs a set of criteria needs to know a range and it needs to know what it's looking for in the range again I know this is a cross sheet formula because it has referenced another sheet right here so let's go ahead and reference that other sheet and I will say that this is coming from the transactional I just want to count the number of times John is showing up here I'll call this the employee range and the next thing I can do is I can hard code it I can say the word John with quotes that'll give me the number I'm looking for but if I'm gonna autofill it down it'll be a lot slicker if I don't use John and I just have it point to the cell to the left here like that and I'll hit enter it does the same thing now I can autofill it down and see what everyone's how many times everyone is showing up over here so I can see Lisa has three entries Tom three Paul three neat thing about this is that as things are changing on my transaction log this is changing to more useful great we know the number of times they're entered but what I really need to know is how much do I need to pay right here so let's go ahead and do a sum if same thing as a count if exact same thing except that just requires one more row to do its adding machine right now we're just triggering a counter but we need to be totaling things up so what I'm gonna do is I'm gonna type in some F and I'll open parenthesis it needs a range and a criterion actually you know what I could do I'll go right over here and I'm gonna just copy this range in criterion right here just what I did offer this countif because they're the same so if I do some F I'll paste in my range and my criterion and oh it needs one more thing it needs what is it going to be totaling up so let me go find that here I'll go ahead and reference another sheet and what we're gonna be totaling up is the total column I'll just say something like total range and I'll insert the reference now if I hit enter well let's format this I'm gonna highlight the whole column and I can find let's see where am I there it is thank you thank you all right and I'll autofill this down so again this is going to be this will be changing as as new entries are coming and this will just be my totals but here's the thing when I'm doing this payroll I got a break out I got to do payroll taxes so I got to figure out what they did as far as billing in Washington what they did in Texas so let me break these in a little further so if I need to do more than one criteria I'll do a plural not count if but count ifs count ifs allows me to do multiple sets of criteria before I trigger the counter so I'll go ahead and copy this exact thing as I did it right here I'll take this I'm gonna steal it I'll put it over here and paste it in here and I want it to look over or not at that first cell we're on row 7 so I'll say here we go all I'm gonna do is put a plural here count if this allows me to add other sets so I'm looking for yes I'm looking for John but I also need to look for also Washington so I'm gonna go ahead and add the next grouping and by the way some people can argue because you can say why don't you just use countifs all the time you can use one set you can use a lot of sets you could count if the singular version is a is a backwards compatible thing so if you want to get into a habit you can say yeah I'm just gonna go with plural all the time and it'll always work whether I do one set parameters or several sets but let's go ahead and try this so we're looking for John right now we got this if I hit enter right here just what I have so far it's a 10 but I need to refine that to also do Washington so I'm gonna go back in here and I'll add in the next set I'm going right in here and doing a comma and I'm gonna reference the other sheet again this time I call this the state range I'll click on the bill state state range and what I'm gonna do is I'll hit insert reference and what I'm looking for I could hard code Washington right here and hit enter and it'll tell me incorrect arguments hold on a second thank you there we go there we go so there's five so John has five in Washington now if I autofill this down it'll take all this range what I want to do when I autofill it before I autofill it I'm actually gonna point it instead of saying Washington right here I'm gonna point it to this cell but I want to when I do my autofill I want to lock Washington John Lisa Tom can go down I just want to lock wash in place so I'm gonna put a little dollar sign in front of Washington so now if I autofill straight down here we go and I can click on any one of these to see what's doing yeah it is looking at the right area I can also take this so that I'm not reinventing the wheel and I'll copy this and I'll go ahead and put it over into down here but I'll readjust what we're doing where we're looking at row twelve for the employee and we're looking now this time we're looking at Texas which I was on row 11 and I'll just autofill this down I'll zoom out a little bit if you guys hopefully you can still see that can you still see those values in the back of the room okay all right so that's great again I got my counts but what's really more important to me is the amount that I owe so what I could do is I'm gonna take this exact data so I don't have to use it and I'm gonna do a sum ifs I'll take something like this and copy it over here and this time I'm going to call it some ifs I'm gonna get an error when I do this just so you guys know because it needs that one extra value of what - someone both of these are happening let's see I need to start with the range that I'm summing so I'll go ahead and use this this range so I'm gonna reuse this range right here this total range I'll copy it and I'm gonna go here I paste it there we go now it should work I'll audition we're going down and I can do the same thing now I'm getting the numbers I want and the beauty of this is that I can also all know that this is this is always fresh data as the transaction logs are getting longer this is always going to work for me so let's go ahead and look at let me just adjust my cell pointer and I'm looking for Texas which is row 11 looks good great now I know now I know what I need to be paying for payroll taxes here so this is an example of the most common cross sheet formulas that we use I'm gonna go over here and jump back over to our slides real quick so I want to come sort of as we kind of come in to an end I want to just talk about some of the basic tips that I'd love for you guys to take home with you one is our nested if statement use a text editor when you're building some sophisticated nested if statements and when you're trying to figure out the order try and start with the most common occurence first and on the cross sheet formulas just remember that the countif is exactly like the sum if on the plural versions are the same it just you just got to add one extra column in there so that it can add something and remember to fill your sheet reference name so it's easier to use the ranges it's a lot easier to construct those and so to recap we talked about the if statement and the supporting functions and we've also talked about the cross sheet formulas one thing I would like to leave you guys with those of you that are heavy into formulas is in our template gallery if you haven't done so already I want to go over to this and I want to push this little plus sign which takes us to our template gallery and just look up formula examples and click on the one thing that comes up I'm going to use this so I can have my own sheet and see all of the formulas that smartsheet has and they all live in this green column I'm gonna zoom out a little bit so you can see it a little bit better but right here all the formulas live in this green column so for instance simple addition I could double click in here and say oh it's adding these two values these are live formulas if I was to type a value you can go all the way down and see everything and then for those of you that are used to Excel or Google sheets if you want to see the formulas that are unique to smartsheet you can go down to around world 100 over here and you're gonna see the hierarchy formulas if you need to do any kind of formulas with figuring out children's cells or parent cells or the whole lineage the ancestry you can do that with these hierarchy formulas and yeah I'll repeat that part because this is this is the one that you really need to get so what I'm doing is this is this is a smart cheat formula example so if you push the plus tab on your smart sheet it takes you to the template gallery where we have all of our templates and I'm just going in here and doing a search for formula examples and hitting enter and this is the only thing that'll come up and when you click on this you can then push the blue use button which will create a sheep for you that you can always have an experiment with on this highly recommend it for those of you that really want to get really proficient with formulas this is a real helper I still use it all the time let me go back over here so you can always reach out if you're having really getting flustered with any kind of formula help or anything you can always go to our help center at help.getpassages.com for word buy low sell high anytime we have issues with our formulas it always gives you like one of two errors and it's usually the unpossible error but it makes it really hard to figure out what the actual issue is it could be anything from an actual incorrect logical test to just like a missing bracket is there any chance smartsheet or is there anything on the roadmap where we can kind of have some hints as to what could have or suggestions on what we could have done wrong with the formula to help us track our errors quicker we've we get that a lot we internally love to be able to debug our formulas a little bit better rather than just a blanket error that says sorry it's not working yeah I get that I don't know what it looks like on the roadmap but that's one of the reasons why I like to do these in baby steps just little pieces at a time because then you can tell what's working and what's not a lot of times you might be getting an error because you're doing some kind of a date column looking up a date maybe one of the cells at row 1,000 doesn't have a date or someone typed the word a word in there and it's and it's breaking the formula so it requires just a lot of more sleuthing but is long one thing you can always check is make sure you have the same number of open parentheses as closed parentheses make sure they're you're dividing out your commas okay those are usually the biggest sources of error and then breaking it down into little pieces is really the best I can advice I wish I could tell you that they're coming down with a better debugger I just don't know all right over here yes depending you so the question is is there a specific formula that we use to populate percentage complete if if you're talking about the percentage complete when you turn on dependencies on a sheet you can't control you've given control when dependencies are on on the sheet the behavior changes completely and so there is no control over the parent rows are weighted averages if you want to do your own type of weighted average you can make a percent complete as a different separate independent column you can do that we have it so yeah if you want to do some custom customizations like that that's something we can help you all do offline with you though yeah yeah that's so the question is is there a way cuz sometimes one of the issues that sometimes we have is if if when a new row comes in and and it's not copying down the formula because maybe the row above it didn't have one it breaks that whole formula chain when you need to have that there and the only thing I can say is on something like that it does happen that could be a training issue or you could lock the column but I you know you have to edit this stuff and so it happens sometimes on that you just there's not I don't have a good answer to to fix that other than know why it happens no we don't have a the formula at this time we don't have a formula that will let you will return if there's an attachment associated on the road yet question over here okay so when I learned about vlookup that was a huge game changer and told when I started using it I ran into a capacity limit so I couldn't use after a certain amount of using vlookup the number of links or a link limit right is there any plans to increase that link the best thing I can say to that is a lot of times that happens when you're not reusing the range so let's say you just you didn't name the range and you just did this vlookup on this cells and then you did another vlookup that you remade a new range over here and another one over here you're making these link redundancies going on and you're exceeding your capacity that way so the best advice I can give on that is make one big giant range and reuse that name over and over and you should not bump into that or it'll help it'll help it you can edit ranges but no not at this time of to see where we have a better range manageability but right now you can edit existing ranges so yes hello I'm I'm pretty good with the nested if statements to change the ball colors but what I get tripped up on is when I want to have that same concept trip like the risk flag or you know so like if it's so many days past turn it red and make the risk flag check which is fine you can do that but you also have to put in a formula in the wrist flag okay so over that so you if you're if you're trying if you're trying to behave change the behavior of a risk flag and also change the behavior of Harvey balls right you need to have a formula behind the Harvey ball and one behind the respect right but what I can't find is how do you identify to turn the risk flag on oh like a certain logic to turn because a risk flag on or off is 1 or 0 ok if you're always unsure cuz we have a bunch of symbols column and so a lot of times you don't know oh that's what is that the half you know PI symbol or whatever you trying to figure out what do i code to figure out what that is all you have to do is pick some text cell and say equals and click on whatever symbol you're using then flip the symbols around and then you can see here this is what I mean you can say something to this effect is you can if I don't know what the value of let's say I put in a new column and I put in a symbols column and I'm trying some new symbols out like for instance oh the risk flag like this I could say okay if I want to know when this is on or off like this I could say equals this oh I happen to know the risk baggage of zeros and ones it's a boolean and so that's that's not gonna work let me just try something really quick here yeah it I would just say on risk flag or anything that only has two values that's either zero or one but if you're doing anything more complicated it's usually a text value like green is really stored as the text green but the symbols column turns it green no let's see here I will look at it real quick and kind of tell you what is are you talking about this one yeah or this one oh yeah it could be let's try that one I could just all I have to do is turn it to something low or high I'm guessing it's it's the word low or high but it's gonna I'll just say equals whatever this thing is over here hit enter yeah it's it's the word low or the word high yeah and this one I just pointing it over there I'm just saying this equals whatever that cell is and I'm in a text box and it's telling me what the text value is over there because if I had now that what that tells me is I can say the word high and hit enter and it'll get it just turns it into a visual exclamation mm-hmm to save time I've noticed when I do that if I'm croffer at cross-referencing another sheet and then modify that range in another cell it changes all the other cells I'd have to see that I'm not quite clear like are you saying when you autofill down the it'll you know the range like I'm looking at this column and I want to copy that cell and put it over in the next cell over and I don't want to look at this column anymore I want to look at the next column over and I change the range and the formula that I just copied it changes the original formula yeah yeah you'll have to rebuild that that's something when you do croshy formulas the what you expect to be static and dynamic can change so yeah you'll have to do what you just did - to do that I don't have a better answer for you okay yeah yeah you can so that's an example you could use so the question is I was using I wasn't using any hierarchy but if we were using hierarchy like the parent row was Washington and then and then it was all the children rows underneath it using hierarchy I could have referenced I could have just said equals the parent of my existing self or referencing that specific cell so there's two ways I could do it that you could do that in in that sheet with the green that formula example sheet down in the hierarchy it has that exact example when you are looking at the reference sheet and this is kind of the same with report builder to pull up the sheet that you're looking for all of the options are currently expanded and I have a lot of sheets we're scrolling through lots and lots of options is it are we looking at a road map where they could be collapsed where it would make it easier to search for the sheets that you're looking for I don't know the answer to that one sorry hey there in your nested if statements can you talk about using a TRO and when to use it and when not to use it over here what was the question I'm sorry in in your nested if statements can you talk about when to use a TRO and when not to use a TRO so that's a I was dying to use the atro function today but that's an advanced topic and the advanced class they'll talk about that yes to that specific question if you already know about how a TRO works I always recommend using that when possible and and so yes you could I that's normally what I'm always using when I can it's it's just a quicker fun things speed up when you do that but then the advanced classes are really going to talk about that over here hi there my question is that other than attending the beginning class that intermediate class and then also using the samples I feel like there's still sort of a gap in my learning how to use these more effectively I mean obviously there's trial and error and I understand it's just part of the process but I still feel like there could be something maybe it's smart she could do like my formula work but kind of thing where you you know where you where you're able to practice or see more complex examples because while the the function sheet has a lot of the basics I don't feel like it has good examples the way you did here was really really helpful but I want more of that and I don't really know where to get it and if that's through smartsheet or through you know a book on Amazon like I really don't even know where to start because I've never wrote I've never written formulas for excel it's not like I have any sort of background at all coming and getting cold there's a few sources so we have a new Learning Center if you go to the help section in there's a few I'll name a few sources you can go to so we have a help a new help and learning section that you can go in and and search for some form is where we have a bunch of new videos coming out because of a new the interface changes you'll be seeing we have a forum where you can post questions and other folks like yourself can answer questions like that those are probably the three top ways to be and and like I said be on the lookout for formulas it's the number one thing that people call in and ask about if we could do formulas and so we're definitely aware of that and we're trying to make it more more available for you guys but there's already resources right now and don't feel free to ask any of us as well yeah and I appreciate the you know you can ask the community or you can see what's been posted on blogs but I feel like I want I want something a little more proactive in a way than oh I'm having a problem and now I need to ask for help I mean again I understand it's part of the process but it'd be nice if there was something where I could sort of practice or have more samples or you know try and build this formula and see what works and then we'll show you the right formula after you've screwed up where it's just sort of like a workbook I mean I I don't know I'm just sort of feeling like there's this traditional way of learning this stuff which people have done for many years but there's maybe is a better way I don't have anyone else inside depending on what you're trying to do we have practice workbooks and things of that that's what I mean and if you're really looking specifically for certain things like that to really you know catch on to certain things like that just you reach out you can you have my email okay and so you can reach out to me directly at least point you in the right direction yeah you the smartsheet certification course goes over formulas and there is a worksheet that comes along with that where you can practice and then you have it to refer back to that's a really good answer thank you so can I ask you a question yes I ran into a problem where my formula it was large what do you know character limit on formulas I'm my I think it's four thousand the same as the text limit I could be wrong I can double check on that but it's that's a if you're running out of space on a foreign but that's a pretty big formula now worst case scenario is you can build another column that has maybe half of the formula if you had to and do like a what we call a helper column if that's the case but that's a lot if your I'd love to see that's formula I like that I was like II the big ones I know they're scares my team will have time for one more question everybody anybody all right thanks a lot thanks everybody [Applause]