Locked lesson.

Upgrade

About this lesson

A cool trick that shows how we can dynamically look up pictures and return them to our worksheet

Exercise files

Download this lesson’s related exercise files.

VLOOKUP() for pictures.xlsx
75.1 KB VLOOKUP() for pictures - Completed.xlsx
81.9 KB

Quick reference

VLOOKUP() For Pictures

Adding dynamic pictures to your dashboard.

When to use

Use when you want a picture to change based on user selection or updated data.  An excellent example might be where you want to update weather icons based on the current forecast.

Instructions

Setting up the pictures for use

  • Begin by setting up a picture worksheet
  • Place the names of the pictures in column A, and the pictures themselves in cells in column B
  • Ensure that the pictures fit entirely within the cell
  • Select the range that holds the names and pictures column A and B
  • Go to Formulas --> Define Names --> Create From Selection --> Left Column
  • Verify that selecting a picture’s name from the Name box takes you to the cell

Set up a driver cell

  • Go to your Dashboard page and select the cell that will drive the picture you want
  • Click in the name box and type a name for your cell
  • Go to Formulas --> Name Manager, locate your name and click Edit
  • Adjust the formula so that it reads as shown below:
    • =INDIRECT(SUBSTITUTE(<your original formula>," ","_"))

Copy a picture cell to the Dashboard Page

  • Select one of the pictures cells by selecting it from the Name drop down box
  • Press CTRL+C to copy (it is important to copy the cell, NOT the picture!)
  • Go to the dashboard, right click and choose to Paste Special --> Linked Picture
  • Select the new picture
  • In the formula bar, type the name of the cell you set up as your driver

Hints & tips

  • Add a data validation list to your driver cell to allow a user to pick value from a list to drive the picture choice
  • A great source for the data validation list is the list of names you have beside your pictures!
Login to download
  • 00:04 In this module, we are going to look at, what I call vlookup for pictures.
  • 00:09 And the concept here is, wouldn't it be cool if,
  • 00:12 when we changed our product line, we had a little picture that popped up over here
  • 00:17 that actually showed us what product line we were dealing with.
  • 00:19 A little bit more eye candy.
  • 00:21 And as it happens on the images page,
  • 00:23 I happen to have some images that I've set up for the product line.
  • 00:28 Now, the key thing here is that these images, when you select them,
  • 00:31 must live entirely inside the cell.
  • 00:34 They cannot overlap the boundaries or borders, okay,
  • 00:37 that's the first thing to be aware of.
  • 00:39 So every one of these lives within the cell, they're entirely inside.
  • 00:44 The next thing is that each of these pictures needs to have a name.
  • 00:48 So I need to be able to pick that name off of the drop-down list.
  • 00:50 Right now I have a name defined for products,
  • 00:54 which I did by grabbing this guy here and just typing products in the box.
  • 00:59 Now, that on it's own is irrelevant.
  • 01:02 The main reason that that's actually there is it's used in our drop down list as
  • 01:06 the product names to pick from this particular list of valid items.
  • 01:11 But, what I need is I now need a name for
  • 01:14 this cell for tents, this cell for sleeping bags, and so on.
  • 01:19 So here's the quickest way to make this work.
  • 01:21 We're going to select this range, and we're going to go to formulas.
  • 01:26 Now we're going to chose to create names from the selection.
  • 01:31 When we do that, it says you'd like to create names for
  • 01:34 values in the Left column.
  • 01:36 So what it's gonna do, because I've picked two columns up, is it says well,
  • 01:40 the column on the right is gonna be named what I see on the left.
  • 01:43 And when I say OK, nothing seems to happen except that from my drop down list,
  • 01:48 you'll notice that I have a few more things.
  • 01:50 I've got tents, it'll take me to that cell.
  • 01:53 I've got tarps and ground sheets and it'll take me to that cell.
  • 01:56 So this is kind of an interesting piece here.
  • 01:59 Now the next piece that's really important
  • 02:02 to know is that this is selecting the cell, this is not selecting the picture.
  • 02:06 What I'm gonna do right now is I'm going to say Control+C to copy this.
  • 02:10 I'm now gonna go to the dashboard page and I'm gonna right-click, and
  • 02:14 I need to go to Paste Special, and I need to create a linked picture.
  • 02:19 This is really important, it must be a linked picture, not anything else.
  • 02:23 At that point, I've got a nice little picture here.
  • 02:27 Now, the challenge is that this guy is pointing back to images.
  • 02:31 And I need to do something slightly different.
  • 02:33 What I'd like to do is,
  • 02:34 I'd like to actually have something that points back to my product line.
  • 02:39 So I'm gonna give this particular cell here a name called Product.
  • 02:45 So right from the area here, I select the cell, type in the name box product.
  • 02:49 And now I can go anytime to this name box and choose Product,
  • 02:54 and it will take me to that cell.
  • 02:56 But there's actually something kind of important that we have to do here.
  • 03:00 We've gotta change things up a little bit,
  • 03:02 because right now I can't actually make this picture read from that product.
  • 03:06 It needs to be a very special formula.
  • 03:09 So what we're gonna do is we're gonna go over to the Name Manager
  • 03:12 on the Formulas tab.
  • 03:13 And we're going to find Product, and we're going to click Edit.
  • 03:17 And the important piece here is that Product right now reads from dashboard Q4.
  • 03:23 I need to wrap this in a very special function called INDIRECT.
  • 03:28 And what INDIRECT will do is it will evaluate the name that's in this cell
  • 03:33 on the fly when it's needed.
  • 03:35 It's kind of a very curious function, that doesn't have a ton of uses, but
  • 03:38 when you do have a use for it, it actually works really well.
  • 03:42 What's interesting here is that it changes, no longer a value,
  • 03:46 it shows {...}, which means very little to us, but we'll say Close.
  • 03:52 We'll select our image and now,
  • 03:55 instead of saying images here, we'll change this to be Product.
  • 04:01 Now we want the product with the name tag, not the product with the formula.
  • 04:05 And now we'll say enter and you'll notice that it brings back Tents.
  • 04:10 And that's pretty cool.
  • 04:11 It works for Tents, it works for accessories, but
  • 04:16 unfortunately it doesn't work for sleeping bags.
  • 04:20 Why? It also doesn't work for tarps and
  • 04:23 ground sheets.
  • 04:24 Why?
  • 04:25 Well the issue is all around the name.
  • 04:29 If you notice, Tarps and Groundsheets, when it got a name,
  • 04:31 got underscores in in it.
  • 04:33 And the actual value in the cell here does not have underscores.
  • 04:36 So we also need to make one more modification to deal with that
  • 04:39 little problem.
  • 04:41 We're gonna go back into the Name Manager, we're gonna go back to product, and
  • 04:46 we're gonna edit it.
  • 04:48 We know the dashboard Q4 is bringing us tarps, space, and, space, groundsheets.
  • 04:52 We can see that in the cell itself.
  • 04:55 So what we're gonna do is we're going to change this formula a little bit.
  • 04:59 And I'm just gonna make this box a little bit wider, so we can see it all.
  • 05:02 We're gonna use a function called SUBSTITUTE.
  • 05:06 Now, it's unfortunate that we don't actually get IntelliSense in here, but
  • 05:09 basically what we're gonna do is we're gonna take the value in Dashboard Q4,
  • 05:13 and we're gonna SUBSTITUTE, quote, space, quote.
  • 05:17 So our spaces with quote, underscore, quote.
  • 05:22 And then we'll close off the brackets here.
  • 05:25 So what's that's gonna do, is it's gonna replace every space that's in here
  • 05:28 as it pulls it in with an underscore.
  • 05:32 And when we say OK and Close, we now get our tarps and groundsheets.
  • 05:37 Because even though the cell says tarps and groundsheets, we're replacing that
  • 05:41 with underscores, which means that it can find this particular name.
  • 05:45 Now if you don't have the spaces,
  • 05:46 you don't have to worry about that last little piece there.
  • 05:48 But those are the key parts that actually make this whole thing work.
  • 05:51 And what's kinda nice about it now is that we can flip through and
  • 05:54 we get a little bit of eye candy on our dashboard to flip to our Tents, and
  • 05:57 our Sleeping Bags, and all the other products that we have.
  • 06:00 It updates with our emojis and all of our different visuals and different charts and
  • 06:04 whatnot, and really adds a lot of flair to the data that we're actually bringing out.

Lesson notes are only available for subscribers.

Gift this course