Create a Dependent Drop List With No Source Data

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.

A screenshot of a cell phone  Description automatically generated

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:

A picture containing drawing  Description automatically generated

Naming the list of countries

Select any single country, then expand the selection to the entire list by using the shortcut CTRL + A

A close up of a sign  Description automatically generated

On the Formulas tab of the ribbon, click on “Create Names from Selection”

A screenshot of a cell phone  Description automatically generated

The “Create Name From Selection” dialog box opens ► the box for “Top Row” is checked ►Hit OK

A screenshot of a cell phone  Description automatically generated

Creating our main drop list.

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 screenshot of a cell phone  Description automatically generated

A drop list is created, test your drop list by selecting any country.

A screenshot of a cell phone  Description automatically generated

Assigning a Geography Data Type:

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.

A screenshot of a cell phone  Description automatically generated

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.

A screenshot of a cell phone  Description automatically generated

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)

A screenshot of a cell phone  Description automatically generated

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”

A screenshot of a cell phone  Description automatically generated

There are 2 things to note:

  1. The Capital also has related data to extract if we wish so, it has the Show card icon and Insert Data options tag.
  2. Selecting any cell were we extracted information shows a formula in the formula bar

A screenshot of a cell phone  Description automatically generated

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 screenshot of a cell phone  Description automatically generated

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.

A screenshot of a cell phone  Description automatically generated

Note: if there are obstacles on the way, we get a Spill error.

A screenshot of a social media post  Description automatically generated

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.

A screenshot of a cell phone  Description automatically generated

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.

A screenshot of a cell phone  Description automatically generated

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.

A screenshot of a social media post  Description automatically generated

Final Notes:

  1. 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.
  2. 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

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

SUBSCRIBE TO OUR EMAILING LIST AND RECEIVE FREE BOOK

Receive my Amazing Textbook “Pivot Tables From Zero To Hero” Totally FREE when you subscribe below to our emailing list.
SUBSCRIBE NOW
close-link