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):

• Banking sector sample – stock codes: ANZ, CBA, NAB, WBC
• Energy sector sample – stock codes: ORG, PWW, RIA, WPL
• Food Beverages & Tobacco sector sample – stock codes: FRM, GNC, GFF, PFL

A two stage selector panel

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

It comprises:

• Selector stage 1, Sector (row 5), and
• Selector stage2, Stock code 1 – the Analysis stock (rows 13), and Stock code 2 / Index – the Comparison stock (row 18)
• With names:
  • 1.1 Sector, Refers to: ='StockAnalyser - validation demo'!$E$5
  • 1.2 Stock_Code_1, Refers to: ='StockAnalyser - validation demo'!$E$13
  • 1.3 Stock_Code_2___Index, Refers to: ='StockAnalyser - validation demo'!$E$18

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

• SectorList
• With names:
  • 2.1 SectorList, Refers to: ='StockAnalyser - validation demo'!$J$5:$J$8

• StkCode1List
• With names:
  • 3.1 ALL (SectorSelector1), Refers to: ='StockAnalyser - validation demo'!$K$11:$V$11
  • 3.2 BAN_Code_1, Refers to: ='StockAnalyser - validation demo'!$K$12:$N$12
  • 3.3 ENE, Refers to: ='StockAnalyser - validation demo'!$K$13:$N$13
  • 3.4 FOO, Refers to: ='StockAnalyser - validation demo'!$K$14:$N$14

• StkCode2List
• With names:
  • 4.1 ALL_2 (SectorSelector2), Refers to: ='StockAnalyser - validation demo'!$K$17:$W$17
  • 4.2 BAN_2, Refers to: ='StockAnalyser - validation demo'!$K$18:$O$18
  • 4.3 ENE_2, Refers to: ='StockAnalyser - validation demo'!$K$19:$O$19
  • 4.4 FOO_2, Refers to: ='StockAnalyser - validation demo'!$K$20:$O$20

WorkArea – sector selector

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

• The analyst has decided to use the first 3 characters from the SectorList items (case insensitive) to drive selector 2. Details are located in the SectorSelector area of the WorkArea.
• The formulas are:
  • ALL, formula =UPPER(LEFT(Sector,3))
  • ALL_2, formula =UPPER(LEFT(Sector,3))&"_2"
• Functions used: LEFT and UPPER.

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.