Do a picture lookup in Excel.
Today's dueling Excel podcast shows how to do a picture lookup in Excel.
Today's dueling Excel podcast shows how to do a picture lookup in Excel.
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it is another Dueling Excel podcast, I’m Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun is going to be joining us, and you are going to learn so much today! The question: How do we do a picture lookup? So we choose a value, and depending on which value we choose, the picture changes, and Mike and I have dramatically different ways of doing this. I’m going to show you how I set this up, and then we’ll throw it over to Mike, and Mike will show us how he did it.
So now, first of all, use Data Validation to make sure that someone chooses from this List, so I have a list of books up here, A1:A3, alright so that gets us our dropdown. The next thing I need to do is I need to add a comment to this cell, so we go to the Review ribbon, click New Comment, and I’m going to get rid of all of the words, no words in that comment. Let’s make the comment a little bit taller, we’ll move it out a little bit, so that way we can see it all the time. And now check this, I’m going to format that comment, I want to make sure that the comment is selected with dots, and not diagonal lines.
If you have diagonal lines, it means you’re editing text, we want to click on the diagonal lines to change them into dots.
I’m going to press Ctrl+1 for Format, go to Colors and Lines, Fill, and then Fill Effects, alright, so I can put a Gradient there, or a Texture, or a Pattern, or a- Oh, check this out, a Picture! Alright, so I have pictures of the three books, click Insert, click OK, click OK, alright, check that out. Now we have a pop-up picture that appears over that cell, sweet, isn’t it? Alright, now I’m going to right-click and say Show/Hide Comments, that’ll keep the comment always there, your choice whether you want to do that or not.
OK now, next we’re going to do a little bit of code, a little bit of VBA code that says “When this cell changes, we’re going to change a picture attached to that comment.” So I’m going to switch over to VBA with Alt+F11, usually, code goes on its own Module sheet, but not this time, this time we’re going to go to the sheet where we want the magic to happen! So I’m here on Sheet1, we would choose from the left dropdown Worksheet, from the right dropdown Change, so this is a little bit of code that’s going to run every time that a cell changes on this worksheet.
Now I want to paste here, Ctrl+V, this is the code that I wrote before we started, just to make sure that it’s working.
This says “Hey, check and see if the Target.Address cell has just changed, C6, target is a variable that Excel is giving us. If it is, go find the name of the new picture, so it’s this path hardcoded, and then Range(“C7”)- Oh, I didn’t tell you about C7, let’s take a look there. Down here in C7 I have a VLOOKUP, the reason you can’t see the VLOOKUP is because I changed the font color to white, let’s change that back so you can see it, and that is actually getting the file names from over here, so they were all hidden, Ctrl+Z, Ctrl+Z, alright. So, and then Target, that’s the cell we just changed, .Comment.Shape.Fill.UserPicture is a method, and we specify the new picture.
So once we have that code in, choose a new value from this cell, it gets the new file name, loads the new picture, and it’s a snap, like that. Alright so, that’s my VBA solution, putting a picture in a comment, using VBA to change the comment, even the Data Validation. You are going to find amazing tricks with Mike’s method, I like Mike’s method, I’m already giving him the point, so Mike, show us what you have!
Mike: Thanks MrExcel! Hey, you’re going to get the point, and you’re going to split it, because this is not a trick I thought up by any means. microsuez from Malaysia actually sent in this link to excel.tips.net AND this trick only works in ’97, 2000, 2002, 2003, NOT 2007! But it does work in 2010 which is what I’m using right here, so this trick does not work in 2007. And I tried this trick a few times, microsuez sent this in, and it is a little buggy, you know, couple of times I did this trick, and it didn’t work, and I close the Workbook then open it and it was working. So, buggy trick, but kind of interesting nonetheless.
Now point#1 about this trick, you got to insert the pictures into the workbook, which means if you have a 1000 of them, switch to the VBA version. And 2nd, when you insert a picture, it’s got to be not inside the cell technically, but the cell’s got to be sized so that the outside of the cell is completely around the picture. Let’s go ahead and insert a picture, do this Bellen, this is a particular product in our lookup product table, make it nice and small, alright, so we have our 3 pictures.
And point #2 is you have to name the cell, because we’re going to use that name in the formula to get the picture to act as a lookup, so I want to name this Deuce, this Bellen, and this Sunset. I already have the names in the cells to the left, so I highlight all the cells. I’m going to use the keyboard shortcut called Ctrl+Shift+F3, Create Names from Selection, THIS cell will be given THAT name, click OK. Let’s test it, I click there, sure enough I see Sunset, arrow up, Bellen, arrow up, and I see Deuce, so the names are working.
Now I want to do Data Validation, so I have a dropdown here, I’m going to use my same trick, Ctrl+Shift+F3, using the Top row, so now this is named Product. Click on that cell, Alt D L, Tab, L, Tab, and I’m going to hit the F3 key to paste a name. F3 does not work everywhere, it does work in Data Validation though, click OK to paste the name, so now I have my dropdown. And I have a VLOOKUP here, just looking up the Price and the Description, and the last thing that we need is our picture!
I’m actually- We need any picture here, and then we’ll put a formula inside the picture, actually before we do the picture, let’s do the formula, we have a Define Name formula.
Guess what? This is named Bellen, that’s Text, and this cell has a defined name Bellen.
Well the formula that’s going to be inside the picture is going to look here, but that’s Text, and we really want it to look here.
The INDIRECT function can take text that represents a range and convert it to array. To put the formula into defined name, I’m going to use Ctrl+F3, New, the Name will be PictureFormula, how about that? And the formula will be down here. Equals, actually it’s already got the cell there, so I’ll just put the INDIRECT function, ( ), I’m going to click OK, and I’m going to go ahead and test it, click this Collapse button right here. And sure enough, it’s looking at the Bellen word, and it got converted to the range, and so it’s highlighting that, that’s a good trick when you’re doing defined names, to click on that Collapse button, click Close.
Last thing is we need our picture, any picture will do, so I’ll point to this one and hold Ctrl, click and drag. Now I’m going to select the picture, come up to the formula bar, and type my name, F3 won’t work here, (I think it was Picture, right) PictureFormula, and then hit Enter. Now here’s what I mean by buggy, that should be working right there, if I Ctrl+S and close this and go ahead and open it back up now, sure enough, look at that! Click on this Bellen right here to this Sunset, oh, look at that, Deuce, Bellen.
I’m going to go ahead, because I did that named range right there and I have Data Validation, I can actually cut and paste this on to another sheet. Make sure that the picture completely within all the cells when cutting, Ctrl+X, Ctrl+V, and I have my little product lookup here with the big picture, select whatever I want here. Alright, so I guess that’s a 3-way tie, well actually VBA gets .5, and then excel.tips.net and microsuez from Malaysia is getting(?) a third of a point, alright, see you next trick!
Bill: Mike, always great tips! Hitting Minimize when we’re defining the name to make sure that the thing is there, that is cool, and look at this boomerang, it is so lifelike in the screen, it’s almost like I can reach out and grab it! Check that out! Hey, did you, probably if you’re watching, did you know that Mike was a 5-time world record holder in the boomerangs, this is actually a boomerang that his company used to make, and now he is ExcelIsFun! So hey, if you ever need a boomerang, check out Mike’s boomerangs, an amazing set of stuff.
Now Mike, I laughed when you said this doesn’t work in Excel 2007, that means that Excel 2007 is kind of like the Lost Lucy episode (?). It’s the only version of Excel that doesn’t have a File menu, and the only one where this trick doesn’t work, but I’m a little bit confused because I got it to work in Excel 2007, I used a different method.
I came up here and chose this cell, Ctrl+C, and then came down here and went to Paste, As Picture, Paste Picture Link, and you see there’s a formula there. So I used your same trick of setting up INDIRECT, MyPic, press Enter, and now when we come here and change this to B3, that’s where my INDIRECT is pointing, I get the other picture. So it is working in 2007 using basically the old Camera tool, so we’ll have to compare notes there, but anyway.
So points all around, and points to Mike for, you know, 5-time world record holder. As a kid I always wanted to be in the Guinness Book of World Records, tried snatching quarters off my arm, I got within 2 of the records and then some guy, like, did 65 instead of 39. So Mike, I’m extremely jealous, a million points to you for having that world record.
And hey, thanks to everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it is another Dueling Excel podcast, I’m Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun is going to be joining us, and you are going to learn so much today! The question: How do we do a picture lookup? So we choose a value, and depending on which value we choose, the picture changes, and Mike and I have dramatically different ways of doing this. I’m going to show you how I set this up, and then we’ll throw it over to Mike, and Mike will show us how he did it.
So now, first of all, use Data Validation to make sure that someone chooses from this List, so I have a list of books up here, A1:A3, alright so that gets us our dropdown. The next thing I need to do is I need to add a comment to this cell, so we go to the Review ribbon, click New Comment, and I’m going to get rid of all of the words, no words in that comment. Let’s make the comment a little bit taller, we’ll move it out a little bit, so that way we can see it all the time. And now check this, I’m going to format that comment, I want to make sure that the comment is selected with dots, and not diagonal lines.
If you have diagonal lines, it means you’re editing text, we want to click on the diagonal lines to change them into dots.
I’m going to press Ctrl+1 for Format, go to Colors and Lines, Fill, and then Fill Effects, alright, so I can put a Gradient there, or a Texture, or a Pattern, or a- Oh, check this out, a Picture! Alright, so I have pictures of the three books, click Insert, click OK, click OK, alright, check that out. Now we have a pop-up picture that appears over that cell, sweet, isn’t it? Alright, now I’m going to right-click and say Show/Hide Comments, that’ll keep the comment always there, your choice whether you want to do that or not.
OK now, next we’re going to do a little bit of code, a little bit of VBA code that says “When this cell changes, we’re going to change a picture attached to that comment.” So I’m going to switch over to VBA with Alt+F11, usually, code goes on its own Module sheet, but not this time, this time we’re going to go to the sheet where we want the magic to happen! So I’m here on Sheet1, we would choose from the left dropdown Worksheet, from the right dropdown Change, so this is a little bit of code that’s going to run every time that a cell changes on this worksheet.
Now I want to paste here, Ctrl+V, this is the code that I wrote before we started, just to make sure that it’s working.
This says “Hey, check and see if the Target.Address cell has just changed, C6, target is a variable that Excel is giving us. If it is, go find the name of the new picture, so it’s this path hardcoded, and then Range(“C7”)- Oh, I didn’t tell you about C7, let’s take a look there. Down here in C7 I have a VLOOKUP, the reason you can’t see the VLOOKUP is because I changed the font color to white, let’s change that back so you can see it, and that is actually getting the file names from over here, so they were all hidden, Ctrl+Z, Ctrl+Z, alright. So, and then Target, that’s the cell we just changed, .Comment.Shape.Fill.UserPicture is a method, and we specify the new picture.
So once we have that code in, choose a new value from this cell, it gets the new file name, loads the new picture, and it’s a snap, like that. Alright so, that’s my VBA solution, putting a picture in a comment, using VBA to change the comment, even the Data Validation. You are going to find amazing tricks with Mike’s method, I like Mike’s method, I’m already giving him the point, so Mike, show us what you have!
Mike: Thanks MrExcel! Hey, you’re going to get the point, and you’re going to split it, because this is not a trick I thought up by any means. microsuez from Malaysia actually sent in this link to excel.tips.net AND this trick only works in ’97, 2000, 2002, 2003, NOT 2007! But it does work in 2010 which is what I’m using right here, so this trick does not work in 2007. And I tried this trick a few times, microsuez sent this in, and it is a little buggy, you know, couple of times I did this trick, and it didn’t work, and I close the Workbook then open it and it was working. So, buggy trick, but kind of interesting nonetheless.
Now point#1 about this trick, you got to insert the pictures into the workbook, which means if you have a 1000 of them, switch to the VBA version. And 2nd, when you insert a picture, it’s got to be not inside the cell technically, but the cell’s got to be sized so that the outside of the cell is completely around the picture. Let’s go ahead and insert a picture, do this Bellen, this is a particular product in our lookup product table, make it nice and small, alright, so we have our 3 pictures.
And point #2 is you have to name the cell, because we’re going to use that name in the formula to get the picture to act as a lookup, so I want to name this Deuce, this Bellen, and this Sunset. I already have the names in the cells to the left, so I highlight all the cells. I’m going to use the keyboard shortcut called Ctrl+Shift+F3, Create Names from Selection, THIS cell will be given THAT name, click OK. Let’s test it, I click there, sure enough I see Sunset, arrow up, Bellen, arrow up, and I see Deuce, so the names are working.
Now I want to do Data Validation, so I have a dropdown here, I’m going to use my same trick, Ctrl+Shift+F3, using the Top row, so now this is named Product. Click on that cell, Alt D L, Tab, L, Tab, and I’m going to hit the F3 key to paste a name. F3 does not work everywhere, it does work in Data Validation though, click OK to paste the name, so now I have my dropdown. And I have a VLOOKUP here, just looking up the Price and the Description, and the last thing that we need is our picture!
I’m actually- We need any picture here, and then we’ll put a formula inside the picture, actually before we do the picture, let’s do the formula, we have a Define Name formula.
Guess what? This is named Bellen, that’s Text, and this cell has a defined name Bellen.
Well the formula that’s going to be inside the picture is going to look here, but that’s Text, and we really want it to look here.
The INDIRECT function can take text that represents a range and convert it to array. To put the formula into defined name, I’m going to use Ctrl+F3, New, the Name will be PictureFormula, how about that? And the formula will be down here. Equals, actually it’s already got the cell there, so I’ll just put the INDIRECT function, ( ), I’m going to click OK, and I’m going to go ahead and test it, click this Collapse button right here. And sure enough, it’s looking at the Bellen word, and it got converted to the range, and so it’s highlighting that, that’s a good trick when you’re doing defined names, to click on that Collapse button, click Close.
Last thing is we need our picture, any picture will do, so I’ll point to this one and hold Ctrl, click and drag. Now I’m going to select the picture, come up to the formula bar, and type my name, F3 won’t work here, (I think it was Picture, right) PictureFormula, and then hit Enter. Now here’s what I mean by buggy, that should be working right there, if I Ctrl+S and close this and go ahead and open it back up now, sure enough, look at that! Click on this Bellen right here to this Sunset, oh, look at that, Deuce, Bellen.
I’m going to go ahead, because I did that named range right there and I have Data Validation, I can actually cut and paste this on to another sheet. Make sure that the picture completely within all the cells when cutting, Ctrl+X, Ctrl+V, and I have my little product lookup here with the big picture, select whatever I want here. Alright, so I guess that’s a 3-way tie, well actually VBA gets .5, and then excel.tips.net and microsuez from Malaysia is getting(?) a third of a point, alright, see you next trick!
Bill: Mike, always great tips! Hitting Minimize when we’re defining the name to make sure that the thing is there, that is cool, and look at this boomerang, it is so lifelike in the screen, it’s almost like I can reach out and grab it! Check that out! Hey, did you, probably if you’re watching, did you know that Mike was a 5-time world record holder in the boomerangs, this is actually a boomerang that his company used to make, and now he is ExcelIsFun! So hey, if you ever need a boomerang, check out Mike’s boomerangs, an amazing set of stuff.
Now Mike, I laughed when you said this doesn’t work in Excel 2007, that means that Excel 2007 is kind of like the Lost Lucy episode (?). It’s the only version of Excel that doesn’t have a File menu, and the only one where this trick doesn’t work, but I’m a little bit confused because I got it to work in Excel 2007, I used a different method.
I came up here and chose this cell, Ctrl+C, and then came down here and went to Paste, As Picture, Paste Picture Link, and you see there’s a formula there. So I used your same trick of setting up INDIRECT, MyPic, press Enter, and now when we come here and change this to B3, that’s where my INDIRECT is pointing, I get the other picture. So it is working in 2007 using basically the old Camera tool, so we’ll have to compare notes there, but anyway.
So points all around, and points to Mike for, you know, 5-time world record holder. As a kid I always wanted to be in the Guinness Book of World Records, tried snatching quarters off my arm, I got within 2 of the records and then some guy, like, did 65 instead of 39. So Mike, I’m extremely jealous, a million points to you for having that world record.
And hey, thanks to everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
