Data validation w/ multiple lists


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


Example


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.


xlf-data-validation-sector
Fig 2: Sector selector Sector - SectorList (item 2.1)

xlf-data-validation-sector-selector1
Fig 3: Sector selector 1 (item 3.1) - Stock Code 1

xlf-data-validation-sector-selector2
Fig 4: Sector selector 2 (item 4.1) - Stock Code 2 / Index