Display Product Image Upon Code Selection
“How to display product image when the product code was selected?” A group of participants from the retail industry asked that question during one of the past trainings. 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.
- 1Type 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.
- 2Place the corresponding images for the products in Column B.
- 3In Cell H2, create a dropdown list using the data validation function to allow user to select the product code from the list.
- 4Using 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.
- 5When 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.
- 6But 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.
- 7After 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.
Download this “display product image” file and find out how it works.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
