Subscriber only lesson.
Sign up to this course to view this lesson.
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.
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!
Lesson notes are only available for subscribers.