Data validation w/ multiple lists

This module demonstrates the use of data validation with multiple level Source lists.


The example is relevant to the xlf Stock Analyser project.

Three industry sectors are used, with sample companies listed on the Australian Stock Exchange (ASX):

A two stage selector panel

A truncated version of a Stock Analyser selector panel is shown in figure 1, range C4:F20.

It comprises:

Fig 1: Excel Web App #1 sector selector panel - columns C to F, and Workarea - columns F to W

The selector stages are setup in the WorkArea. There are 9 defined names that will be linked dynamically to the data validation cells

WorkArea - sectors

WorkArea - sector selector

When the user selects a sector, the second stage is handled as follows:

Selector - data validation

Data validation uses Allow: List, Sources items as shown in figures 2, 3, and 4. Note the use of the INDIRECT function (figures 3 and 4) with the text references in SectorSelector1 and SectorSelector2.

Fig 2: Sector selector Sector - SectorList (item 2.1)

Fig 3: Sector selector 1 (item 3.1) - Stock Code 1

Fig 4: Sector selector 2 (item 4.1) - Stock Code 2 / Index