Geography Data Types and Data Validation
Office 365
We learned in previous examples different ways to create a dependent Data Validation list. Whatever the technique used, the source data used for the main and dependent lists is available in the workbook itself.
In our example, we will be creating a dependent drop list without having the source data anywhere in the workbook. The source data is extracted live over the internet using the Geography data type. Microsoft recently introduced new Data Types in Excel: Stock and Geography and they are available on the Data Tab of the ribbon only in Microsoft© Office 365.
In the exercise file we have 2 sheets. On the “Source” worksheet I have a list of countries in column A. That is all what we have as source data. I will be using this list to create my main drop list in the Geography worksheet.
You can Download the Exercise File and Follow along by clicking on the Button:
Naming the list of countries
Select any single country, then expand the selection to the entire list by using the shortcut CTRL + A
On the Formulas tab of the ribbon, click on “Create Names from Selection”
The “Create Name From Selection” dialog box opens ► the box for “Top Row” is checked ►Hit OK
Creating our main drop list in Excel
Go to the Geography worksheet and select cell A2.
Click on the Data tab of the ribbon ► Click on Data Validation (Data Tools group).
Under “Allow” select List and in the “Source” box type the named range and hit Ok: =Country
A drop list is created, test your drop list by selecting any country.
Assigning a Geography Data Type to a Drop List:
In Microsoft 365 (Formerly called Office 365) we have a group of “Rich” or “Linked” data types, both names really emphasize what these data types do.
To acquire the new functionality and be able to connect to online sources and get information related to the selected country, go to the source worksheet and select the list of countries (without the heading). On the Data tab ► Click on Geography” in the Data Types group.
Excel connects to online sources and a little trifold icon appears to the left side of each country name.
This is the “Show Card” icon. If you click on it (or use the shortcut CTRL+Shift+F5) ► it opens a card that offers a wealth of information related to the selected country.
Scroll down to explore what this Geography Data Type can offer. At the bottom of the list, you can check were this data came from.
On the right side of the selected country, there is an option Tag called “Insert Data”. Clicking on this options tag display a menu of related information that can be inserted in the worksheet (to the right side of the selected country)
If you click on any option, Excel inserts the information in the first empty cell to the right side of the selected country.
In the Figure:
1- Show Card 2- Insert Data 3- Related Information menu
If we switch to our main drop list in the Geography worksheet and change our selection ► we get the same functionality.
Click on the “Insert Data” options tag ► Click on “Area”
The options tag is still available so let us repeat 3 times to select “Capital”, “Leaders” and “Population”
There are 2 things to note:
- The Capital also has related data to extract if we wish so, it has the Show card icon and Insert Data options tag.
- Selecting any cell were we extracted information shows a formula in the formula bar
This means I can also extract information for the country by typing a formula.
In cell I2, we are going to extract the Regions or Subdivisions of that country (you can consider it States or Provinces).
In cell I2 type the formula:
=A2.Subdivisions ► Hit Enter
A list of provinces is automatically extracted.
If you select a different country from the Data Validation drop list in A2, everything automatically updates.
Note: if you see the text #Busy in the cell , that means Excel is retrieving the information (Be patient).
The extracted list of Subdivisions, is a Spilled Array range that spills to the cells below I2 as far as needed.
Note: if there are obstacles on the way, we get a Spill error.
We are now ready to create our dependent drop list of Regions in Cell A8.
Select Cell A8
On the data Tab, click on Data Validation
Under “Allow” Select “List”
In the “Source” Box, refer to cell I2 and the entire Spilled array by typing:
=I2#
The # sign refers to the spilled array.
Hit Ok and test the dependent drop List.
Select an Option from the dependent drop list.
Not only the list is created and includes whatever number of provinces returned in the dynamic array in I2, but it also inherits the functionality and icons of the Geography data types. With that said, we can continue extracting information for the selected region, such as population.
Click on the Insert Data options tag for cell A8 and from the menu select “Population”.
Test by switching options from the main drop list then from the dependent drop list.
Final Notes:
- Changing your selection from Cell A2, does not automatically change the contents of cell A8 until you make a selection, although the dependent drop list is updated.
If this issue bothers you, it requires a Change event code in VBA to fix it. - To add mystery to your project, hide column I and the “Source” worksheet
You can watch this tutorial live on my YouTube channel, YouTube.com/officeInstructor
