Data validation w/ multiple lists plus Stock DataType
This module demonstrates the use of data validation with multiple level Source lists.
Data validation selector :: 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
- 1.1 Sector,
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
- 2.1 SectorList,
- 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
- 3.1 ALL (SectorSelector1),
- 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
- 4.1 ALL_2 (SectorSelector2),
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"
- ALL, formula
- 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.



Data Type Stock link to selector :: example
To display up-to-date stock data, we use the Stocks DataType available on the Data tab (Fig 5). The Stocks fields lists include: 52 week high, 52 week low, Beta, Change. Change (%),Currency, Description, Employees, Exchange, Exchange abbreviation, Headquarters, High, Industry, Instrument Type, Last trade time, Low, Market cap, Name, Official name, Open, P/E, Previous close, Price, Shares outstanding, Ticker symbol, Volume, Volume average, and Year incorporated.

Two versions of the Stock DataType are shown. An in cell version in Figure 6, and an image version in Figure 7.

The image version is on the Drawing Layer, so it can be positioned independently of the grid layout.
The Camera Tool source data in on the same worksheet as the target picture. Company logos are included the the Banking Sector in the image version. Some ASX codes included in the original Excel file (Figure 1) are invalid mostly due to new names for the companies. The out-of-date names are identified as Unlisted in the worksheet (Figure 8)

An image of the revised Excel file is included in figure 8.

- Download the file: from figure 8: xlf-stock-analyser-sector-selector-plus-stock-type.xlsx [128 KB] LOCKED
- Published: 1 May 2015
- Revised: Wednesday 6th of September 2023 - 02:03 PM, [Australian Eastern Standard Time (EST)]