There were several questions came into my inbox asking on how to make flags in my World Cup 2014 spreadsheets changed dynamically based on country names. I have made a draft on how to create that function one year ago but never had a chance to finish and publish it until now. Because I think, I would rather make a video version of this tutorial to accompany my tutorial article.
Here is the step on creating the excel function, without macros, that will make you change image dynamically based on certain cell value. There are two excel functions used in this spreadsheets (VLOOKUP and INDIRECT) to form dynamic image function. And with a simple trick to combine it with Linked Picture function, you can easily implement it into your excel spreadsheet. You can find online resources or your excel help file to understand those excel functions, if you need more explanation about them.
I will show you how to do it in simple steps. You can start from a blank spreadsheet. Watch youtube tutorial to understand it better. You can read steps below to check something that you might miss when you follow my steps.
First Step : Preparing Flag Image Table
– It is assumed that you already have images that you will use for your project. Here, I used flag images that represent countries in Group G in World Cup 2014 tournament.
– Create a 4 x 3 table (B2:D5). First Column (B) is allocated for flag images, second (C) is for country name, and the third (D) is for image cell reference.
– Insert flag images into this spreadsheet and put the first image into cell B2. Image has to be fit within cell B2. You have to resize the image and the cell to make sure that there is no part of the image is lied outside its cell. I inserted “Portugal” flag image.
– Resize the flag to your target size, here I resize it into 0.5 x 0.5 inch.
– Change row size to accommodate that image size, here I put 42 as its row size.
– Type flag name in C2, “Portugal”
– Type image cell reference, including worksheet name, in cell D2 -> Sheet1!B2
– Repeat steps above for the remaining 3 rows.
Second Step : Preparing Flag Image Place in Standing Table
– Assumed that you already have a standing table. Here, I used Group G standing table.
– I copied the standing table, but I take rank numbers and country names only (G2:H5). Rank numbers in cell G2:G5, and country names in cell H2:H5.
– Insert one column between rank number and country names, between G and H column. The table will be expanded into 3 columns (G2:I5).
– Adjust its cell size to have the same size with flag image cell size. Since I use the same row, I don’t need to adjust it.
Third Step : Preparing Flag Image Dynamic Reference
– Create another column, it could be in this worksheet or anywhere. Here, I create it next to country names (J2:J5). This column will be allocated for flag image reference where its content will be changed based on country position in standing table.
– Create Names for formulas that refer to those cells. Create a formula that should take that cell content as flag image reference. Use INDIRECT function to map the content inside the cell into a cell reference. Without INDIRECT, any reference formula to get the value in that cell will process its content as text characters instead of a reference.
– Here I name it Flag1, and replace the value in “refers to” box from any content into =INDIRECT(Sheet1!$J$2).
– Do this step 3 more times until all 3 remaining cells (J3:J5) are corresponded with names.
Last Step : Implementation
– Select the the original flag image cell (B2). Copy the cell (press Ctrl+C). Remember to select and copy the cell, not the image.
– Go to flag image cell in standing table (H2).
– Right Click your mouse and paste (paste special) it as a linked picture.
– Select the picture, go to function box, and replace its value with Flag1.
– You can test it by changing country name in country name cell. You should see the flag is changing based on your input.
– Do this 3 more times to complete all flag image references in those cells.
Those are all steps you need to follow to create the dynamic image reference function without macros. You can download my working spreadsheet that I used to create this tutorial. You can compare your spreadsheet with mine if it doesn’t work well. And if it works well, you can start to play around with formulas, layouts, etc to make sure that you can use it for your own projects.
Is there other alternatives? Yes, there is. You can use index function to skip dummy cell reference in standing column, but in my experiences, it consumes more excel resources when you are working with plenty of images.