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


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

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.


xlf-data-datatypes-stocks
Fig 5: Stocks item is available on the Data tab, DataTypes group

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


xlf-list-validation-and-data-type-demo2-small
Fig 6: Stocks Cell version - rows 22:26

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)


xlf-list-validation-and-data-type-demo1-small.png
Fig 7: Stocks Camera image version, on the drawing layer at rows 28:50

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

xlf-list-validation-and-data-type-grid-view
Fig 8: Excel WorkSheet image - the source data is contained in hidden rows of the WorkSheet (29:43).