00:01 So when we're creating sheets and we're adding some functions and formulas to our sheets, sometimes we want to do something in the future, but we like know that we wanna do it.
00:11 And so we can set up some formulas and I've been doing recently a lot in spreadsheet automation using app script using code.
00:19 But I wanted to go back to some formulas that I think feel like magic. They feel like automation. They, they really seem like you're adding automation to your sheets.
00:28 Meaning that if you as a user or another person using your sheet, take some action or enter some data, something happens based on that action.
00:40 So as a result, like a, like an effect, right? An action and an effect. Now we can do a lot of this in app script, but if you are uncomfortable with code, if you're uncomfortable diving into app script using on edit on open and these sort of builtin functions and you're sort of not really convinced you want to learn to code, I wanna give you some interesting formulas and formula pairings that help you make essentially automation inside your sheets.
01:11 They will at least seem like automation or seem like magic sometimes. First off is the if, and we're gonna go through all four of these if flatten, if error filter.
01:21 We're gonna talk about transpose and unique one of my favorite types of things to do because it sort of creates headers.
01:27 If we're gonna create literally something out of nothing, flatten takes a bunch of like mangled data, turns it into a single array and we'll talk about that or column 'em.
01:37 And if error and filter is really cool because you can set up filters before you know someone's gonna fill it out.
01:43 And then as if like magic, their information is filtered once they sort of go to another page. So we'll get into this, let's get into it.
01:52 Let's start off with the, if the simplest way I can show you this is if we create like a to-do list, our task list inside of sheets, and we have a checkbox here.
02:02 Coolest thing about checkbox is that they are a visual representation of the values of true and false. We can create, in here we use equals if a logical expression in our if formula needs to be something that ends up being true or false, but check boxes are true or false.
02:21 So we can just reference the checkbox A two. Now, if the value is true, meaning it's checked off, it'll show something.
02:30 If it's false, it'll show something else. So one thing you can do in this particular case is say if it's true write some kind of like done like a a word and if it's false we can sort of just say whatever, like task one here, whatever the task we're trying to do, what that allows us to do is when we hit this checkbox, this is done.
02:55 We can also conjure it from nothing. So We can write this all the way down. We can also put all of these tasks on a separate tab and then reference their cell here instead of the word task one, we can reference a cell like B two here.
03:12 Now as we go down, we'll see these are done, so we can keep these b this be column on another tab if we really want to.
03:20 But we can see that this is done right, that changes our text just based on that, that action of checking off.
03:26 But we can also show things. So let's say we don't want to see task two through 10. Basically we want, we don't wanna see the next task until we're done with one.
03:37 So what we can do is equals same thing a two, but in the true, actually we'll do task one, we'll leave as is for task two.
03:47 We'll do equals if our logical expression is going to be the row above it, A two, and we're going to say if the value is true, show task two, if it's false, meaning there is no checkbox check mark we are gonna do nothing.
04:04 We're gonna leave this absolutely blank right after the comma. We le don't do anything there and we're gonna hit enter.
04:10 Now when tasks one is done, check task two shows up. So we're going from some nothing to something. We're literally conjuring up here this task two based on the checkbox in on A two.
04:26 And we can do this all the way down again, we can, we don't have to write in a a, a, the string here, the words here, we can reference something else.
04:35 So we can reference say B two or B three, sorry here, and we can do this all the way down.
04:42 So now I copied it, there are formulas here, but it looks like there's nothing. And as we go Task two, we're done, we show up task three, alright, click another one, we're done with that.
04:53 Task four shows up. This is a really cool way to really bring some automation in, right? Show the next task when the task is done, or some kind of approval steps.
05:02 You might be able to do some like fun quizzes in this, really some cool use cases here. That's if, and we, and we really conjure something up from nothing.
05:12 Something from nothing. Now flatten is a little less exciting. I I will say let's create another tab and we have some, you know, task, maybe we're writing down our notes, but they're in like many different columns, right?
05:28 We have sort of this data that's mangled up. We have task three here. We have task four, we have task five up here.
05:38 One of the interesting things is with Google Sheets is sometimes maybe not interesting, maybe some of, one of the heartbreaking things is we will use somebody else's data, right?
05:51 Somebody else will put something in a sheet and we have to analyze it. We have to manage that data, we have to like figure out, wrangle it up, right?
05:59 Like a cowboy with some, some stray cows or some stray sheep have gotten out. And of course, if I wanted to put this all in list, I would do something like task one, task two.
06:11 I would just like look at these and write them somewhere else. And this is gonna take a while, but what I can do is equals flatten.
06:18 And what this will do is we'll take the whole range here and see now everything's in one column. Now, there, there does include the blanks here, right?
06:28 Because sometimes our data is absolutely full. Like we have task two, task three, task four, five, task six, right? And if we do it like this, it will actually, if these are all in order vertically, but we're putting them all in one column, this will end up being actually it has to go across, sorry.
06:51 So you see here, task one, task four is across horizontal. Each row is getting the column and then it's adding them together all in one column.
07:03 This is really cool. Again, if you have a whole bunch of mingled data, you would also want to have something like sort, perhaps you can sort this sheet you know, undo something like this.
07:15 You can also sort that will help you. But this flatten is the key here. Flatten is gonna take a wide array of like ranges, right?
07:23 A, a two-dimensional array or range, right? Columns and rows, and it's gonna put it all in one place. This is really cool because we don't have to put flatten in after the data comes in.
07:36 We can put flatten in a sheet or maybe on another tab and say, okay, here's three or four columns that you're gonna put any information you want here.
07:45 And then on this other tab, it's going to capture all of that and put it in one column. Very, very useful for certain types of tasks or data entry when you're not really sure, like maybe you just wanna see a, a wide range of data here.
08:01 Very, very cool to add Flatten somewhere where, you know, the data that's coming in is across many different rows and many different columns specifically.
08:11 All right, next one is a formula pair if error and filter. Now what happens with filter, we'll do something like this.
08:21 We'll actually take our, we'll rename these. This is, if this is flatten and we'll add another one filter. You might be familiar with filter, but I wanna show you one extra thing you can do with if, if error.
08:38 Basically task two, task three, maybe there's task four and we go equal and then we're like done. We'll write done here, done.
08:51 And then, and task done. We can do equals, sorry, equals filter. We're gonna filter out a column, but we're gonna say only ones in the A column that the B column is equal to done.
09:08 So we only wanna see the done ones, the done tasks, and we can neatly list done. Now this is cool, right?
09:16 We can add this filter in and now if we have a list of tasks, we can even increase however many are here.
09:24 As we get them done, we get a list here, we don't even have the spell done with the capital D.
09:29 If we get our tasks here, they will automatically show up in this filter. That is if we use the filter for the entire column, right here is the problem.
09:41 If we have no tasks done, if this filter is empty, we're gonna get a an error here. And this is the error is na, it means no matches are found.
09:52 So it's not really a, a real error. I mean it is literally an error in in that there are, there's nothing there.
09:58 It can't show anything, but it's not an error because we're gonna fill in something. So you use, you can wrap if error, you can use the formula if error, wrap it around filter.
10:10 And instead of saying like some text here, like you can literally do nothing, you can just add that comma, hit enter and now it's blank.
10:23 So it doesn't show anything until somebody starts typing in. Done, right? We're, we have these done tasks Duns, not dones, done.
10:33 Now that's pretty cool, right? We're we're, it's essentially conjuring a filtered list based on your list that you have here.
10:41 Like that's really cool. You don't have to show the filter, you don't have to show an error. You can do if error.
10:47 I'll show you one extra thing if however you do in encounter other errors other than an A and you want to show that, see if error is gonna happen no matter what the error is.
11:00 But we actually know the actual error is na. So you can do if n a here. So if n a is essentially the exact same thing as if error, but it will only do the, if it'll only cover the if n a error.
11:17 And that's really cool because then if for some reason our filter, we actually do have a problem with this syntax, if we have a problem with it here, it will show up and we'll see, okay, we have an error, we have to fix that.
11:30 And so if na is sometimes better, if you're confident about your formula typing and your syntax, and I would always recommend doing it first without the if error or the if na, just do filter alone, get the error and then put the wrapper on it.
11:51 That's what I would recommend. Cool. Now, transpose and unique, this is probably one of my funkiest and funniest flavors of formula pairings.
12:02 It's using transpose, which is a really fun weird <laugh> sort of weird formula here. Let's retype that. Transpose better transpose.
12:16 All right, transpose what it does it do, it takes ta Let's do our task list again, task one, task two, let's create a little bit, few more.
12:27 And if we do equals transpose and we just take in B one B two to B six, what is it?
12:35 It puts everything horizontal. It literally just changes from a column to a row. Well, if we have some kind of categories, let's say for these tasks, like there is home tasks, there is office tasks, there are, you know, baby, we have to take care of the baby for some of these tasks.
13:00 And we are like, we want to prioritize, let's say the baby stuff today. Like we're, we have a task list and we're like, here's our categories of what this task is, what this thing we need to do.
13:12 And we want to create what's sort of like a combine board or a Trello board. It might be where we have home office and baby.
13:25 And we'll list underneath here. We'll use our filter. I actually have a whole nother video about this check out bond board or Trello board in, in Better sheets.
13:33 I have a whole nother video about this. We'll put a filter down here, but this headers here.  What happens if we end up having like a different task, right?
13:44 We have a car task we add, we could programmatically or automatically add that to this header list. And that's how we use we use Unique and Transpose.
13:56 What Unique does is we'll take all the unique of a column here is all of our unique, now, if the car didn't exist, it would only show home Office baby.
14:06 But if we add Car, this unique formula is automatically bringing in the car. So this is a list, right? But we want it as headers.
14:18 You might be yelling the answer to me right now. You might be like, it's transpose cuz I just showed that to you, right?
14:24 So what we can do with our unique is wrap it with Transpose and let's see what happens there. And now suddenly we have all of our headers in exactly the way we want them in, specifically in the order that they are vertically, they will be uniquely as our headers.
14:47 And all we're doing is taking transpose and wrapping that around the unique formula. This is really cool. Now, again, if you want to check out the actual combo board video, go check that out.
14:56 Better at Better Sheets. I have a whole video about using Filter here and this particular Formula pair. But this one was supposed to show you automation esque kind of things.
15:07 Bring a little bit more automation to your sheets without knowing App Script. Again if you are not confident about App Script or you're interested in App Script and actually automating your sheets, I just released spreadsheet Automation 1 0 1.
15:22 And I think if you're watching this video I'll, I'll be putting this video actually part of that course there's 29 modules already.
15:31 It's just less than three hours. With this video, we're gonna hit over three hours of content over at spreadsheet Automation 1 0 1.
15:39 Check it out. It's available right now on Better sheets.co. If you're a member, if you're a Lifetime member, you have automatic access to it.
15:46 But also, if you're not a member and you're watching this say on YouTube, go check out spreadsheet automation 1 0 1 and on Udemy, it's available on Udemy.
15:54 Thanks for watching. Bye.