 Of course there are a lot of things going on in Twitter at the moment But one of them is a word all this game where you can guess a five letter word And I thought okay, that'd be an interesting challenge. Can we implement it in a spreadsheet? obviously Without VBA as well. This is just gonna be Neat, so we set a random number up here to generate a new word and then that's let's start guessing them So hotel that's tell me there's an H in it No other letters Shirt maybe Well, it begins with an SNH Rug I'm not sitting played fully But if I could figure this out in real time I would do that and so it works and I thought well, let's not just do a straight-up tutorial about this Maybe I'm just unpack the code behind this and show you what this can do you kind of think a bit programmatically about what's gonna happen so let's Get my grid lines headings formula bar back on and go back and hide a couple of things Oops, I need to actually also unprotect my sheet, so that's on hide That show where that's doing soon Unhide that you can see oh, it's such a check that's the answer and Unhide some stuff down here That's telling me what these letters when I'm striking that and then I'm gonna unhide my word list So the start of this is really you've got to find the right word list This was actually quite difficult to do because some You get lots of different word list crossword solvers and stuff like that and some of them are short Some of them are way too long. They might have thousands and thousands of Five-letter words in but they're actually random letter combinations. They don't work and this I can't remember. I found it I think it was a plain text file. I found hosted this worked really well Because it contains most actual words without going into random letter combinations It's fairly complete and it's also it's already randomized So if I type in a random number up here Number's too high. That's five seven five seven. Let's go five seven seven. That's the top one pupil So it's not gonna be from a to z for instance So what's this happening so the first thing we need to do is select a number of given the List of words here a named range called word list It's the only name range I've used which is quite unusual, but it's in there as one anyway and I've also used it as Sort of a semi-dynamic thing because I've just inserted the entire column. That's what that's referring to And you try to count here. So once it's tried and round to number between one and Count count a being the function to count the number of cells with contents So that will give me the list the total length of that list And I'm only putting the random number in here randomly because if you do a round or round between Updates every time you press the return button So in order to refresh it you would need to use a VBA or a macro type thing And I wasn't going to do that. So you have to put in the number randomly for this version And it will look it up using the index Function you can use X lookup or V lookup for that. It doesn't really matter But index works just fine And then I've got one two three four five here because these are the positions of it And so what I've got is a mid function to try and split this up into individual letters and mid tells you where Tech what text cell it is. So that's a three there The start number Which is going to be one two three or four five and then give return me one character So if I turned that into two for instance, it would give me two characters worth Now I have covered in something previously about How to do that with a spill formula in Excel now, but I haven't bothered in here I've just done this as one two three four five because it gets reused and I know I'm not going to extend this out of five So I'm not going to really make that dynamic or anything like that I could make this based on the length of the word and just Extend it to six laces if I wanted to but it's not necessary And so now here the inputs And those are going to be the guesses now. What am I doing here to create this? Well, this is a monster formula, but I will try and break down What it's actually doing Basically, it is attempting if you can see here. I'm extracting. This is a mid If the mid of that equals The thing up there and I return yes, and then I'm seeing if that's false I'm giving a much more complicated formula here. It's in it. If is there a match I've got that mid thing to extract the letter out again Without reusing a second cell If you can find that in this List here, so there's a match function that tries to find this text in that array that I've opened up All five of those letters separately into an array if you can find that in there It will return a number if it doesn't find it in there it returns an error So I use the is error function and is error returns True or false so I can stick that in an if statement So if it is an error means it hasn't found it it brings up n and then if it does find it m Which I've used for maybe Now what I've then done is if I come up to home and go to my conditional formatting I've made it turn red green or yellow depending on whether it's yes. No, or maybe it's what the n y and M4 and that applies to that entire grid there and to give it a little bit of an effect. I've actually just Created a semi transparent beveled thing to go over the top makes it look a little bit more like the emojis that you'd see on Twitter Right, then is it correct or not? That is simply just count if count the number Count the number of yeses in here, so count if that range is equal to yes And then a little bit more conditional formatting so I was good to manage rules Yes, let's just bring that up. Here we go icon set That's just a standard icon set if it is greater or equal to five tick if it's greater or equal to three Give it a little explanation mark if it's less than that put the X Now to the right of that I've actually got another this is something that checks Whether your input is valid or not So if I put just hot instead of hotel it says not five letters So I've basically put it if the length is not five Not five letters, so this is means difference here and then if that's Checks out and it says it is five letter words. It tries to match it into the word list and Returns if that produces an error the is error triggers it goes not a word I've also put an and function in here with not blank So if I got rid of that and it would actually say not five letters and trigger automatically when he is so Put that and function. I will say in excel Ifs and and statements and if else is actually really difficult. This is really pushing what this thing is designed to do I would not recommend doing a lot of nested if statements with excel This is not the software to use for it. This is just playing around with it to see what it can do not what it is designed to do So that all in mind what I got over here This is doing the same sort of thing. I'm breaking that up with a mid function To produce the the same thing and this word if I didn't have concat here I'm just gonna delete that Briefly I'll just show you how that works. This is what this will spill to the right. There you go HOT in fact, I'll put hotel back in here HOTL But if I then wrap that in Concat that concatenates it and the spill disappears It just actually what this has done is it splits the word up and then concatenates it back together But what you can do then is add an if statements. So Put that back here the if This arrange here this this range in the middle equal. No Yes concatenate it if not Concatenating and nothing and then I've put an ampersand So what this actually does is this keeps a running total Of the number of letters that have been striked out as read with no so you can see all these letters SHCK It maybe I've reused them a couple of times, but this should be a running total of all the letters that have been Declined by it and that is what this grid is counting here because I'm Seeing if I can find a particular letter from this keyboard In this column, I've done a little bit of an indirect function to try and find the highest one the latest one that happens And if it return And if it does find it it will return a number if not it will return an error and I've used that if error again Just to return zero And this is complicated because this is conditional formatting go wrap rules here I've used use a formula where this is true And I've done if b18 is greater than zero Okay, so what this is doing is if this Wasn't that function if this was five or something it's found five copies of it or it's found in the fifth Position it doesn't matter. Um, it will turn gray and strike out. That's conditional formatting And using this relative positioning with conditional formatting can be a bit tricky You got to go to like say a new rule Use a formula to define it Set that cell Remove the dollar signs I'm going to format that Let's just fill it with red for now Just to show it. Okay that now it's greater than five. It'll do that Exactly the same as before and we drag along And then just do fill formatting only It will take that conditional formatting across So you will have to do that maybe in two directions. Oh, I've copied all those letters If I do fill more formatting only it goes back to where it was So Probably go back to that rule. Just make sure I've got the grayed out version So that is a little bit about how you can use a spreadsheet to actually build something like this is a small game It actually does work You just want to hide Your answers hide a couple of Columns that are helping you And what you can do is hide even the fact that it's a spreadsheet by Getting rid of all of those You can hide the Word list. I don't think it helps you too much. Anyway, unless you manually go searching for it and then just Review protect the sheet lock that so now we can't actually select anything except these inputs And what was it again? Pull wasn't it something like that And there we go. I've got a couple of extra bits for me. I just to Um fill this in is all six Uh, so that has all been assembled kind of piece by piece by trialling it and saying, okay Well, what do I need it to do next? And then adding maybe an extra bit in and it is it's incredibly fiddly to do with excel. So this is more like a Approve of concept you weird. Um Yeah, this is what you can do if you want to have a bit of fun with it and try to Learn a bit of programming without doing just all programming It would certainly be easier to implement in python or javascript or whatever You could be a bit more flexible and I think We've you did Did a double letter. I Not sure how this quite reacts to double letters when it finds something twice Um the actual proper game that's that's going around probably handles that a little bit better. Um I suppose a few other things we could do like if We go unprotected again Um Let's put all these back on Unhide that where I've got this conditional formatting being read From a separate grid. I suppose I could also Do this grid separately as well and then reference that And then I could highlight the let put the letters in here and take the conditional formatting from somewhere else This is all about making the conditional format Um based on whether it's returning a particular number Ah, yes, that reminds me. Um, you can probably see These cells are empty They do contain something but they do not display and That is really that's a really useful trick. We've gone. I've added in a more Uh custom format here. So if you go to format cells go to custom and type in three Um semi-colons What that basically does is Translate this into a date But it doesn't set the format of the date and so the kind of the formatting options kind of gives up and doesn't display anything at all Um, so it's a good hack if you want to just use conditional formatting to display something And not actually see the number or the letter behind it And therefore you just get the color And then I'll put this grid over the top of it. Um, it'd be cool if each cell had its own individual border Um, but the borders are shared with each cell So if you did the top right of the cell I've got highlighted It would be the bottom of that one and the left of that one. So you can't quite create a bevel effect Um, that's very reproducible in excel. It is it's I mean it's it's an accountancy bit of software Well, come on. What do you want from me here? Um, but that's how you do it That's how you can implement a small game in it without vba