In Episode #1411, Bill showed us how to use Pivot Table Slicers to display images. Today, in Episode #1417, Bill shows us how to set up the Dynamic Images in Excel Using Paste Picture Link with an OFFSET named range.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1417.
Dynamic Pictures.
Hey! welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last week, on Monday episode 1411.
I showed you this cool trick where we had dynamic pictures, that were appearing in response to the slicers and also, the smart art.
Yesterday, I talked about how to do the smart art, which is interesting.
Five people figure out how to do that but the picture of most people used VBA.
I want to show you this cool trick for having the pictures change in response to basically selecting a number.
So, let's just start off here, we'll get rid of this picture.
Let's say we start with one really tall cell and make the cell a very tall, I want to insert a picture in that cell.
So, if you're in Excel 2010, go to insert, screenshot, screen clipping.
Now, what this is going to do?
It's going to go back to the last active application, so before I do that.
I want to go find the web-page that has the picture that I want.
Make sure that it's in view then we will go back to Excel, screenshot, screen clipping, wait for the screen to kind of gray out and then just use the mouse to draw the rectangle, all right!
So, we've inserted that in our cell.
I'll make a little bit smaller, so it fits.
Alright! So, that's the first step then from there we go down several cells and insert the other pictures in one and each cell, make the cells tall enough to fit that.
Okay! Now, we're going to use the camera tool, used to be call the camera tool.
It's not called the camera tool anymore.
We're gonna copy this cell [ ctrl C ], and then come over to a new spot and we will paste and use the linked picture.
Link the pictures, let me scroll over There, okay!
So, now whatever happens to be in that first cell, will appear in this cell if I would do anything to the first cell like for example change the fill color, you see that it changes over here in the picture.
So, this is a live view.
It's a cool little trick right, but here's the thing that makes it really, really powerful is I'm gonna build a formula, an offset formula.
If we take a look at this formula here, where I start from J-51 and we're gonna go down some number of cells.
So, whatever number happens to be here in K51, in this case down to cells and over zero comes to point to the cell that I want.
Now, I'm not allowed to use this formula unless, it's assigned to a name and when we assign it to a name.
It actually has to use the full sheet name.
So, sheet three exclamation point and in here sheet three exclamation point and we'll add dollar signs.
I'll press the [ F4 ] key, to make sure there are dollar signs everywhere.
So, there's my formula.
I want to assign that to a named range and we do an insert art, now.
Look at that back in Excel 2003 was under insert.
It's under formulas and then define a name and we'll call it cool trick pick and it refers to that area, click [ ok ].
Alright! So, now we're coming along.
Now, I'm gonna go back here and my result, so the camera tool, we're gonna select that.
Right now, this is hard-coded to say it's equal to J52, which is that cell.
I must say that it is now equal to, cool trick pick, and when I press [ enter ] that's gonna change to the state of Florida.
Bingo!
And then all we have to do is have some mechanism, maybe a formula or something that changes the number here, and it will grab the next picture from the range.
Now, again the trick here is to make sure that each of these cells is exactly each of these pictures takes exactly one cell, tall.
So, make the cells taller, the rows taller, in order to handle that maybe just have it off to the side or something like that.
In a very cool way without any VBA at all to instantly update the picture that's shown in response to this value or formula.
Hey! I wanna thank you for stopping by, we'll see next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1417.
Dynamic Pictures.
Hey! welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last week, on Monday episode 1411.
I showed you this cool trick where we had dynamic pictures, that were appearing in response to the slicers and also, the smart art.
Yesterday, I talked about how to do the smart art, which is interesting.
Five people figure out how to do that but the picture of most people used VBA.
I want to show you this cool trick for having the pictures change in response to basically selecting a number.
So, let's just start off here, we'll get rid of this picture.
Let's say we start with one really tall cell and make the cell a very tall, I want to insert a picture in that cell.
So, if you're in Excel 2010, go to insert, screenshot, screen clipping.
Now, what this is going to do?
It's going to go back to the last active application, so before I do that.
I want to go find the web-page that has the picture that I want.
Make sure that it's in view then we will go back to Excel, screenshot, screen clipping, wait for the screen to kind of gray out and then just use the mouse to draw the rectangle, all right!
So, we've inserted that in our cell.
I'll make a little bit smaller, so it fits.
Alright! So, that's the first step then from there we go down several cells and insert the other pictures in one and each cell, make the cells tall enough to fit that.
Okay! Now, we're going to use the camera tool, used to be call the camera tool.
It's not called the camera tool anymore.
We're gonna copy this cell [ ctrl C ], and then come over to a new spot and we will paste and use the linked picture.
Link the pictures, let me scroll over There, okay!
So, now whatever happens to be in that first cell, will appear in this cell if I would do anything to the first cell like for example change the fill color, you see that it changes over here in the picture.
So, this is a live view.
It's a cool little trick right, but here's the thing that makes it really, really powerful is I'm gonna build a formula, an offset formula.
If we take a look at this formula here, where I start from J-51 and we're gonna go down some number of cells.
So, whatever number happens to be here in K51, in this case down to cells and over zero comes to point to the cell that I want.
Now, I'm not allowed to use this formula unless, it's assigned to a name and when we assign it to a name.
It actually has to use the full sheet name.
So, sheet three exclamation point and in here sheet three exclamation point and we'll add dollar signs.
I'll press the [ F4 ] key, to make sure there are dollar signs everywhere.
So, there's my formula.
I want to assign that to a named range and we do an insert art, now.
Look at that back in Excel 2003 was under insert.
It's under formulas and then define a name and we'll call it cool trick pick and it refers to that area, click [ ok ].
Alright! So, now we're coming along.
Now, I'm gonna go back here and my result, so the camera tool, we're gonna select that.
Right now, this is hard-coded to say it's equal to J52, which is that cell.
I must say that it is now equal to, cool trick pick, and when I press [ enter ] that's gonna change to the state of Florida.
Bingo!
And then all we have to do is have some mechanism, maybe a formula or something that changes the number here, and it will grab the next picture from the range.
Now, again the trick here is to make sure that each of these cells is exactly each of these pictures takes exactly one cell, tall.
So, make the cells taller, the rows taller, in order to handle that maybe just have it off to the side or something like that.
In a very cool way without any VBA at all to instantly update the picture that's shown in response to this value or formula.
Hey! I wanna thank you for stopping by, we'll see next time for another netcast from MrExcel.
