00:01 Hello. Welcome. Today's question comes from the Facebook group. I love Google sheets, which is run by me and better sheets.co.
00:10 Uh, you can join. I love Google sheets for free it's facebook.com/groups/i love Google sheets, no spaces, nothing. Uh, just join and you can either share sheets you're working on or questions like these.
00:28 Get your answer. So what Elizabeth is doing here is, um, she was wondering, she's got a drop down list of, I guess, subjects.
00:39 And she's trying to create a little schedule inside of Google sheets. And once, once you select the prop, the class show an image.
00:48 So here she says, uh, in B two is a dropdown menu English. And in B3, she wants the contents of eight, 12.
00:58 So there's a couple of things going on here. Um, one is she's using the hidden row, which I strongly recommend not to do, uh, hidden rows and hidden columns are a little bit of a pet peeve of mine because of this every single time that you hide a row or hide a column, you cannot note what is hidden, nor will anyone else know what is hidden.
01:22 So if you share that sheet with someone, the very first thing they'll do is they'll see that there's a hidden row or a hidden column and unhide it and then see what's there and then hide it again.
01:31 Cause they're like, oh, I need to hide this or they won't remember to hide it. So what I always recommend is if you ever find yourself needing to hide rows or hide columns, you probably want to put that information on another sheet.
01:45 Now, the fact that Google sheets has the ability to hide columns in Hydro's is totally okay. I, I, I imagine there are use cases for it, but 100% of the time that I've ever seen a hidden row or a hidden column might unhide it.
01:59 I look at it, then I hide it again and it's complete waste of time. Uh, what you can do is take that information, put it on a separate tab or a separate sheet.
02:08 And so we'll do that. Uh, first we'll also as one of the parts of this answer. Um, but first I'm going to go through the dropdown, which she actually got the dropdown done.
02:18 Um, but I just want to show in this answer, that one way to do a dropdown is to do a range of cells and that's what we're going to do so we can make it, uh, easy to do.
02:31 All right, let's do a dropdown. So I only saw in her question English, I'm going to make up a, some other, um, some other subjects here, but what I'm going to do is I'm actually going to make this list of potential subjects on a separate page.
02:46 I did call it images, but you'll see why in a second. So I have English study, um, and math we'll we'll do those.
02:55 So there might be a study session in English session in our math session, we're going to go back to our first sheet.
03:01 We're going to right. Click view more cell actions, data validation list from a range. Now this is what comes up normally.
03:09 And I use this a lot. We'll just select the range manually. Uh, I'm going to include some extra column, uh, rows here, because I want to make sure if you ever want to add any, uh, subjects, you can easily do that.
03:24 You don't have to go back and redo this range right here. We'll add some more. If we probably don't have more than 10 subjects.
03:30 And so now we have English available. We also have study and we can then copy and paste this to Tuesday and Wednesday.
03:39 Now the next thing is we need to put in B3 here, an image. Now, what image are we going to do?
03:45 I'm going to go and I'm going to grab, uh, a few images and I'm going to insert them into the cell.
03:51 So I will insert here image in cell. I'm going to upload, uh, I'm going to pause the video. I'm going to upload some actually, no, we won't even do that.
04:03 We'll do, we'll just find them here, but I will pause the video cause it might take awhile. Okay. So I found some Google search image results here from images.
04:13 I can't really tell what these images are. So here's one way to do that, to make that better. So we're going to resize these rows.
04:23 Actually. I think we need to elect the row. So I'm going to select the entire row and resize rows. We're going to resize it to 63 times as much so we can see the image a little bit better.
04:36 And the text is in column a, the images in column B. We're going to go back to our sheet one.
04:43 And in this B3, we're going to do V lookup search key is going to be B2. So we're going to just click there.
04:52 Our range that we're looking in is images, uh, sheet or the tab images, exclamation point. And we're looking in a colon B, we want the range of both of those columns in the images sheet.
05:06 Now the index is going to be two because we want to get the second column. And is it sorted? I always put false here and now we have an image.
05:18 And again, we just need to go to three, five and seven, and we're going to increase the size of this.
05:24 And then when we have an image, we can then take this entire, uh, cell command, see, copy and paste it in.
05:34 <inaudible>. Now the only thing that changes is the before and here in B seven, I'm going to paste it again. And the B6 see that B6 changes to show that it's this column or sorry, this cell.
05:48 So if we do study the image changes. If we do math, the image changes. And now we have a pretty cool weekly planner with images based on the dropdown selection that we have.
06:02 One roadblock you might get into, or one challenge you might end up with is if you insert cell, uh, insert the image over the cell, you won't be able to address that B one on this sheet or on images is an image and it won't transfer over.
06:18 If you want to replace the image, say like this math one doesn't do well, right? Like we can't really tell, or this study one, we want to replace.
06:28 Uh, we can go to this cell. We were going to insert image, image and cell let's pick another Google image, search English class.
06:41 Maybe we want a happy person. And now let's go back to our sheet one and see that updated. Not cool.
06:55 So we can replace these images as we need to. If we want to add more, uh, subjects, maybe we want science.
07:04 If we spell science correctly, we can do science and let's insert an image again. Let's look for a science class.
07:20 Yeah, this is good. Just double click on that. You're going to increase the size of this so we can see it.
07:28 Now, go back to our sheet and say, we want to add a Thursday. And we have science here and our drop down already.
07:37 All we have to do is copy and paste this cell. And we have the science image. And if we say, oh, we don't want English first.
07:44 We want science first. There we go. We've got science and we have English. There we go. Um, this is a possibility because our dropdowns, as we did before view more actions, data validation list from a range, because we did to 10, we can also do just the entire row.
08:07 We can even just take the numbers out and say images eight to eight, save that. And we can paste this, um, data validation.
08:19 And now, no matter where we are on the sheet, we should be able to add another thing. So math to see if that happens.
08:29 There we go. Math too. So we might have up to a thousand or 10,000 subjects, maybe, uh, you might be able to rename them.
08:37 One thing that might happen though, is if you have the same name science and science, and let's say, we have this image as well there for science and you put in science and you're like, oh, science looked good.
08:51 It didn't add another science because what data validation is doing is looking at the amount of options and picking out it's unique.
09:01 So it's you looking at the unique ones that exist? So we cannot add science, science, science, and have five different photos and select the photo.
09:09 We need to assign this a completely different name, science, two science, three biology. And now we can select that out of the data validation on our drop down menu.
09:23 Now it shows up. So those are some issues you might run into with this solution. Um, also make sure that the, uh, formula here B look up, it always uses the first, uh, column as its index, basically index one.
09:43 It always, or it always uses that as the left unique options. So, um, images a to B, we can also do index match.
09:54 Let me try to show you that real quick. Here. We can do that as well. There's another option index. We want, uh, images, B2B comma, and we want to match the search.
10:09 Key will be this, uh, drop down menu. The same thing we're doing is we're looking at the V lookup keep, and what are we matching?
10:17 We're matching images, a colon, a our search type here is zero in parentheses. And we get the same exact thing.
10:30 We can select image English. We can select study and it changes that. So also index match. I like a lot more because you have a little bit more control over it.
10:39 You can actually change this. So you have images first. Let's do that. Let's just make the images first. And the text.
10:50 Second, all of the V lookup will be broken and you can not use a V lookup, but index match actually met.
10:57 Once you said it actually magically changes. Um, so you can move this stuff around and you can still use index match for these as well.
11:09 So we can change this to let's say English. There we go. So index match. I like a lot more. I hope you got a lot out of this video from, uh, I love Google sheets.
11:19 If you want to join, feel free to join our Facebook group. I love Google sheets. Thanks.