This post is on creating a drop-down using the IMAGE function where, if we choose from a list of names, the image gets updated.
To create this in Excel, we’ll use the IMAGE function that extracts data from a web URL and pulls the image into Excel.
The IMAGE function extracts or pulls an image from a source specified into an Excel cell and has the following syntax (per Microsoft).
We have the source URL from the following link that contains the logos of all the NFL teams: https://a.espncdn.com/combiner/i?img=/i/teamlogos/nfl/500/ari.png
If we observe closely we can see that the only part of the URL that needs to be changed id the highlighted section (the team abbreviation)
We have a dedicated article on this topic of image extraction, please do check it out.
Consider we have all the NFL team abbreviations and their names in Excel as follows:
We’ll use the image function to extract all the logos and include an alternate text for each of these images.
=IMAGE("https://a.espncdn.com/combiner/i?img=/i/teamlogos/nfl/500/"&B3&".png",C3,0)The cell B# contains the team abbreviation and cell C3 has the team names. Extend the formula for all the other teams by dragging the formula. Our teams with their logos look something like this:
Let us assign a name to all the logos we just created. For this, select all the cells, give a name “LOGOS” in the name box.
Let’s get to the crux of the topic, the drop-down creation:
Choose a cell where you want to create the drop-down, (1) go to the Data ribbon, (2) Data Validation
Choose the inputs from a list and our source will be the named range we created:
This will create a drop-down list in the cell chosen by you:
We’re able to display the team names in the drop down is because inside the IMAGE function we have included a second optional argument of Alternate Text to refer to these images.
=IMAGE("https://a.espncdn.com/combiner/i?img=/i/teamlogos/nfl/500/"&B3&".png",C3,0)This is a powerful way of using images with alternate texts for easy referencing in Excel.
