0:00 Hey, better Sheets members. Thanks for watching this video. I'm going to share with you how to insert a quote generator or a quote randomizer inside your sheet.
0:09 So bear with me. That sounds a little echo-y, but hopefully you can hear me. Okay. Um, and it's going to be a pretty quick video for, uh, better sheets.
0:19 So how this works, I'll show you how it works right now, and then we're going to get into it. It's one formula or one formula combo.
0:26 I have a list of quotes and anytime you edit sheet, actually, uh, this quote at the top changes, uh, so it can type in anything over here and look, this code changes each time and we can also change the quote every time that the sheet is opened.
0:49 Uh, we can just refresh this page right here. You can see the question is not who is going to let me, it is who is going to stop me.
0:57 And then we're going to reload the page and we can see live as you were to die tomorrow. Learn as if, okay.
1:08 There you go. You can see what's interesting too, is it's not totally random or rather it's not a totally new one.
1:14 It there's actually a random assortment of quotes. You can see the quotes on this page over here, and it is what it's doing is it's grabbing a line or row randomly from this line of code from this page of quotes.
1:28 So here's all the quotes in one single column and then scrapping this, uh, different, uh, row each time. So how has it done?
1:36 I'll show you first, I'm going to show you the formula and then I'm going to break down and build it up.
1:41 So first off it's index quotes a through a column, we're going to round a random number of times. They count of all the rows in quotes and plus one.
1:52 So that's the breakdown. Uh, how did I do that? Well, first off here's what I did is, uh, we'll just do it in this other column here and this other cell.
2:04 All right, let me first show you the random, uh, formula. So we just do equals Rand and we get this number and this is a completely different number.
2:17 Every time you edit, we can actually go over to file. I think it is spreadsheet settings. There it is. Let me share with you a little bit more information about this Ram.
2:33 We're going to go over to the settings for the spreadsheet over to calculation. And we see here, recalculation, this tells you this affects how often now today ran and ran between are updated.
2:43 So we can actually, uh, change this not off, uh, but we can actually change it to even more so we can change every minute, every hour.
2:52 This is really good. If, um, maybe you're making a sheet that displays a new thing and you want it to be new every minute or every hour.
3:01 Maybe you want to change a countdown or a clock or something, and you want it to definitely change or iterate through.
3:07 So you can actually do this. I've done this on a couple of other sheets, like, um, there is a sheet that I built called like three, no code two, three random, no code tools every minute.
3:17 And so you go to that sheet and it has a list of, of no code tools. And then it displays on the one tab, three of them, but it changes literally every minute.
3:25 So you can go back to that sheet. You can keep the sheet open, you'll show you three random, no code tools or Nikki this chain on change for right now.
3:35 But that's some more information about how you can, uh, work with Rand a little bit. So the problem with Rand is that it's going to give you a number between zero and one.
3:44 It is not going to give you a whole number. So what we do, we, if, you know, say, uh, you have a rose of a hundred, you can do times 100 and you would get a numb, the random number between zero and 100.
4:00 Um, you can also round this. So that's what I do. I round it to the nearest number. And you can see here around ran done times, uh, 100, you get a real actual number.
4:15 Um, but we don't know how many rows we have in our folks. So what we do is we can do count or count a, uh, quotes a through a, and now we have an actual number of rows, but multiplied by a random number and then around it to actually get us a random number, which is going to definitely be, uh, one of the rows in our index, in our sheep.
4:45 Okay? Now that we have a random number every time, how do we get that? How do we turn that number into a row?
4:54 We use index function or index formula. We just do index around here. The reference is going to be the same, um, quotes, uh, a colon, a for the H column.
5:07 Uh, the row is going to be our random number. And you can already see here that, uh, it is pulling it.
5:14 You see the preview of the answer. One travels, more U S fully went alone. You don't need the column here.
5:22 So in index, there's three things that we need, uh, or we may want reference, which is the range a row, which is definitely one of the rope, but we can also call them here, but we don't have to, uh, that is optional.
5:36 And so now we have the exact same thing as we have up here and we have it here. And I add plus one because, uh, sometimes this count ball, uh, in this rounding and the randomest doesn't actually get, uh, sometimes there'll be maybe zero, go round it to zero.
5:54 So I want to always have one leader, don't have a row zero. So I always could do plus one. And that we now get the exact same formulas up here.
6:03 And as you can tell, they are different because they're random numbers. Um, one thing about randomization is if you have a small sample size, like less than a thousand, I think you might actually get the same number twice in a row.
6:15 So just watch out for that. If you have anything that actually needs to be different each and every time, like 100%, then you might want a large sample size.
6:24 And even then I've seen sometimes like, uh, has repeats the same number, um, cause it truly is random, not, uh, money, new number.
6:32 So this is, uh, has its limits. Uh, hopefully this was a fun video for you to figure out how to add a random quote to your sheets.
6:40 A few ideas around, this is one you can add a, uh, inspirational quote. If you are creating some kind of playbook, a workbook, a worksheet for, uh, creators or creating something or making some thing, um, write your own, uh, quotes or get your own quotes that are inspirational.
6:59 Maybe you are creating prompts, maybe you're writing something like tweets or, uh, creative prompts. And you can actually put a list of all the creative prompts and creating randomized, uh, she, uh, sheet that is one single one randomized.
7:13 So people don't have to like read down the entire list to find them, uh, you might want to put dates on them, like say, Hey, today's, uh, quote, is this, um, again, you're going to be seeing this change every time you edit the sheet.
7:27 So watch out for that. Um, there are some coding things you can do, like, uh, have it on open. If you know how to do Google apps script, you can have a random thing happen on open.
7:42 Uh, you can, there are some more advanced ways you can sort of limit and have limitations, but I hope this was helpful to you to get started right away within under 10 minutes.
7:52 Um, get a random quote, a random piece of inspiration, a random number I've used similar to this. I've used this many different times, like with a random number generator, uh, that I needed an actual real number.
8:05 Um, I didn't need just like a 0.0 1 5, 3 tour. I needed an actual round number you use round and round.
8:12 Um, that's very helpful to me in many different places. And I hope you have some more ideas in how you can use this.
8:18 And if you do have ideas and you actually put them into use, uh, email me, let them know, let me know what, what you're doing.
8:24 If you're, uh, publicly sharing something like a new quote, a book or inspiration or prompts sheet, uh, please tweet at me, uh, tag me in your tweets and I'm happy to help promote it.
8:37 Happy to help share it. If you use something like this. Thanks so much. Bye.