In one of the past trainings, we had a group of participants from the
retail industry. Their company, which was an American MNC retailer, carried
thousands of products of many designs and for different segments of customers.
For them, it was impossible to know the code of every product. So they wanted to
know if Excel could display a picture of the product when the product code is
selected. That would definitely make the numbers they are analyzing a lot
more meaningful.
At that point in time, I had some idea how it could be done with the
OFFSET
formula and the camera function in Excel but did not have the
opportunity to get down working on it. When I finally did, I was pleased
with the outcome of the solution.Because I am sure nobody ever thought that
was at all possible.
Watch the video and witness how the solution at work.
Type out your product listing and place it Column A.For the purpose of this demo,
I am making use of the product code and images from
Buy Honey Online website.
Place the corresponding images for the products in Column B.
In Cell H2, create a dropdown list using the data validation function to allow
user to select the
product code from the list.
Using the relatively unknown Camera function in Excel, take a camera shot of Cell B2.
You can paste it anywhere you like in the worksheet.
Let's call it the camera box.
When the picture is selected, you will notice that the formula show the address of the
image, i.e. =$B$2. If you change the range reference for the picture to B3, you will see
the camera box replace the existing image to "Organic Honey". Making use of this functionality, we can
make the camera box change image whenever we change our entry in Cell H2. This can be done using
the OFFSET and MATCH formula. The OFFSET formula is to return the address of the image.
The MATCH formula
is to link the OFFSET formula to the dropdown list so that the address
of the picture will change according to the product code given in the cell H2.
But the camera box cannot accept a formula, it can only accept either a single cell
range name. To get around
this problem, you can hide the formula in a range name. For our example, we have hidden the
formula in the range name called pictures.
After selecting the camera box, go to the formula bar and type in without quotes "=pictures".
Test the solution by selecting another product code and see the picture in the camera box
display product image for the selected product code.
