An alternative hierarchy selection method

When navigating through hierarchy selection, often a user would want to see a list of available parents or children, instead of only being able to see one at a time, when looking at a set of drop-down filters. Here is an alternative method using the more complex list box breadcrumb-type approach for three levels.

An alternative hierarchy selection method

Getting ready

Set up the data as shown in the following screenshot. Columns A to C contain the initial full hierarchical data. The sections E to G, I to J, and L each contain a breadcrumb trail whose source is one column less than the parent. More about how everything works will be explained in the How it works... section.

Getting ready

How to do it...

  1. Insert three List Box selectors onto the canvas. On the first List Box selector, which belongs to Region, set the Labels to the values of column A (A2:A13).
  2. Select Filtered Rows as the Insertion Type and set the Source Data to columns A to C. Set the Destination to columns E to G. Notice that we select all three source columns because the first column of the first row of the destination contains the selected region.
    How to do it...
  3. Do the same with the next list box (District); select the labels and filtered rows from columns F to G.
    How to do it...
  4. Finally, the Office list box will be getting its Labels value from the column J. You can either use Label or Row as the Insertion Type as we are only down to one selection column.
    How to do it...

How it works...

In this example, each list box is a hierarchical level. In order to transfer all the appropriate values from one parent to the next data source, we must use the Filtered Rows method to grab all of the parent's children, until the last child, where we can use label or row for the insertion type.

See also

For more detailed information on using Filtered Rows, read the Using Filtered Rows recipe later in this chapter.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset