See the flag for any country

The Link Image widget allows you to show different images on the web page depending on cell values or formulas. This tutorial uses the Link Image widget to show the flag for a new member’s home country. 

In the below membership application form, the new member selects a home country using a Dynamic Dropdown widget. A link to the new member’s country flag is saved with the form. Thanks to the Link Image widget, the right flag is also shown at the bottom of the form.

Try it for yourself! Select your home country in the form and watch the flag change.

If you enter all the required data in the form, you can press the Submit button and see what a form like this looks like when someone submits it to you.

Disclaimer: Working with country data is complicated. Some countries may be more disputed than others. Some countries may have a different official name than you think. Our source for country codes and official country names in English is Wikipedia. Our source for country flags is Flagpedia. For countries without a flag in Flagpedia, we show the flag for the United Nations. Over time, the source may divert from the copy used here. We regret if any part of this tutorial offends you, including the list of countries and their flags. 

Make an inventory of the image links

Open the spreadsheet in Excel

If you have installed SpreadsheetConverter in your laptop, you may want to open the source spreadsheet for the form above in your own Excel now, to see what the spreadsheet source for the form above looks like in Excel. Note: you must install the SpreadsheetConverter add-in to use the spreadsheet, or none of this will be visible.

Download the spreadsheet with the membership application form.

Create a hidden worksheet

Many widgets require background data that you don’t want to make visible on the web page. In most situations, it is very convenient to have one or more background data sheets to keep this data in. In our case, we created an extra worksheet called Countries. Using the Worksheets tab of the task pane, we can easily mark this worksheet as hidden in the converted web page.

Screenshot of the Worksheet tab on the task pane where you can hide background worksheets

Create the image links

We copied all alphabetic and numeric ISO country codes from Wikipedia to column A-C of the Countries worksheet. The country names from Wikipedia are in column D and the country names from Flagpedia in column E.

The link to each flag image in column F is calculated with this formula:

=IF(TRIM(E2)="";"http://www.un.org/sites/www.un.org/themes/bootstrap_un/logo.png";"http://flags.fmcdn.net/data/flags/h40/"&LOWER(A2)&".png")

What the formula does:

  • If this country is not in Flagpedia, use the flag for the United Nations.
  • Otherwise, construct a standard link to Flagpedia with the two-letter ISO 3166-1 country code in column A as the lowercase file name. h40 is the size of the flag image, you can also use h20, h80, h120 and possibly other sizes.

Switch to the Countries tab of the example spreadsheet to see the formula at work in the table.

  • For Afghanistan, there is a flag in Flagpedia, and so the formula assembles a link to it.
  • For the Åland Islands, the formula uses the United Nations flag instead.
  • Kosovo (scroll down) is an example of a country that is disputed, so it’s not in the Wikipedia country list, but since it does have a flag in Flagpedia, we added the flag link manually.

Screenshot of an Excel spreadsheet with a formula to calculate image links for the Link Image widget

Working with themes and design

The electronic forms that you create with SpreadsheetConverter don’t have to be plain. You can use all the formatting options in Excel like column width, background color and character size. Excel doesn’t know much about web page design, so we added a simple Theme Designer to give you full control over the formatting of the web page.

Below is a further developed version of the same membership application form. We just changed the background color to brown, and the foreground color to white.

Photo of a man developing a membership application form in Excel and testing it on his Sony Xperia smartphone

Design the form

Switch to the Form tab in the example spreadsheet to see the simple form we created. Most of the text fields are defined the same way:

Screenshot of a form with merged cells and required text fields

As you can see from the screenshot above,

  • We merged cells A4:E4 to make the “Name” input field wide enough.
  • We inserted a Text widget to open the form field for input.
  • We made some of the text fields mandatory using the Required setting.

We also added a placeholder text, an in-field prompt describing what input we expect, like Enter your full name for the Name field. Using the Name property of the widget, we assigned a name to each input field. When the form is submitted, this causes the field to be referred to as more explanatory member_name and not just something like “A4”. This makes things much easier in the post-processing of received forms.

For the Country selector, we used a Dynamic Dropdown widget. It displays the list of country names from Wikipedia. Using a predefined list ensures that the field always contains valid country names, removing the risk of spelling errors. Notice how the Options Range setting for this widget designates the appropriate cell range from the hidden worksheet. Like for all other fields in the form, we gave the input field an explanatory name.

Screenshot of the settings for a Dynamic Dropdown widget

Obtain the image link

OK, now we have the list of country names in the dropdown, and all the flag links on the background worksheet. Now we need to find the right image link, and provide it to the Link Image widget.

To look up the country, we inserted the following formula in A17:

=VLOOKUP(A16;Countries!$D$1:$F$251;3;FALSE)

What the formula does:

  • It defines a temporary cell range, D1:F251 in the Countries background worksheet.
  • It takes the country name, set by the dropdown in cell A16, and looks it up in the leftmost column of the cell range to find the right row.
  • From this row, it returns the value of column 3 in the cell range (column D counts as 1, E as 2 and F as 3). So, the formula returns the image link in column F for the right country name in column D.

If you only want to pick the right link for a Link Image widget, you can place the lookup cell on the background sheet. But we want the link to the flag to be saved with the form data when the form is submitted, so we put the VLOOKUP in a cell on the Form tab.

It’s nice with a colorful flag in the form, but how do we hide the ugly link?

Screenshot of a Utility widget used to hide a field with a link in a form

Select cell A17 the spreadsheet. You can see the VLOOKUP formula in the cell. But in the task pane you can see that we have also inserted a Utility widget in the cell. This gives us access to the Make hidden field property, which will hide the link in the web page but still submit it with the form. And again, if you assign names to all input fields, you will save a lot of effort in post-processing.

As you can see in the example spreadsheet, we merged cells A19:B23 to create a small container for the linked image. This is where we’ll now insert the Link Image widget.

Insert the Link Image widget

Image widgets like Link ImageGoogle Map or barcode are represented in Excel using an empty placeholder. The placeholder cannot show the correct image in Excel, but it allows you to position the image correctly within the form and give it the correct size. When you convert the spreadsheet to a web page, the widget replaces the placeholder with the actual image it is supposed to show, e.g. the map or barcode.

Before you insert any kind of image widget we always recommend that you merge the cells behind the image placeholder. In most cases it works fine anyway but merging the cells makes it much more obvious to both you and SpreadsheetContainer where the image is supposed to go.

To insert a Link Image widget, select the cell where you want the upper left-hand corner of the placeholder to appear. Make the task pane visible. Locate the Widgets tab and click on the Link Image widget icon to insert the placeholder into the selected cell.

Screenshot of the Link Image widget icon on the Widget tab of the task pane

Select the cell that will contain the image link

When you insert the Link Image widget into a cell, you also need to tell it in what cell it can find a link to the image it is expected to show. Set the Image Reference to the cell that contains this link. In this example, the link is returned by a VLOOKUP formula in cell A17.

Screenshot of a Link Image widget with a placeholder

Adjust the size of the placeholder

You can drag the placeholder to the best position, and change the size of the image as it appears in the web page by dragging the placeholder’s handles. In our case, we made the placeholder the same size as the merged cell area behind it. To adjust the width and height to an exact size in pixels, enter your numbers in the corresponding widget settings and click Apply.

Note: the Link Image widget never distorts the images. If you have a lot of images in various sizes, you can still specify a maximum width and height here. Each image will be resized proportionally to fit entirely within the placeholder.

Convert the spreadsheet

OK, we seem to be set. Save the spreadsheet if you’ve made any changes to it. Perhaps this is a good time to rest a little.

Click Convert in the SpreadsheetConverter ribbon to convert the spreadsheet to a web page. The converted web page usually opens automatically in one or more of your web browsers. Otherwise, make the History list visible in the task pane and open the converted web page from there.

In the web page, select a different country and watch the image change to show the right flag.

Publish the form on the web

SpreadsheetConverter comes with built-in one-click web publishing. Your form is hosted on the distributed Amazon S3 infrastructure with superior performance, extreme reliability and massive scalability. Over a dozen server locations around the globe ensure that your calculator always is within easy reach of your global audience.

Our Publish to Cloud feature automatically generates all the HTML you need to add the calculator to your website.

The iframe we used when we included the form at the beginning of this web page (scroll up!) looks like this:

<iframe src="https://ssccust1.spreadsheethosting.com/1/59/226a21cef25ec5/Address-with-flag-dyndrop/Address-with-flag-dyndrop.htm" width="560" height="740" frameborder="0">

To include the form, we just pasted the above HTML into the HTML source of this web page. To learn more, read the help page for Publish to Cloud or one of the tutorials in the list below.