Using a source list for Data Validation cells is one of the easiest ways to allow a user to select an item from a dropdown list. Sometimes it will be necessary to make the choices available in one Data Validation cell depending on the choice already made in another such cell. A common way to achieve this is to use Range Names and the INDIRECT() function to select from a list of names in one cell and then use that name as the list source for the other Data Validation cell.

The topic was covered recently in the Excel Community. In his Modelling 101 series, Liam Bastick covered a range of Excel functions that could be used when working with dynamic ranges, including OFFSET(), CHOOSE() and INDIRECT(). This article was also the inspiration for a later article covering an alternative approach using Power Query.

Here, we are going to build on both of these approaches to address a specific application of dependent lists: the ability to limit one list to the items that belong to a particular category in another list. We will be using nominal account codes for our example and showing how we could make it easier to create formatted reports by restricting the choices of accounts according to the particular section of the report that we are working with. The approach could be used to work with a range of other accounting issues from choosing product types and options through to staff planning.

Accounts by category

Two years ago, a two-part series in Chartech focused on using Power Query to create a formatted profit and loss account by consolidating data from multiple workbooks. As part of this process, an accounts chart was used to allocate individual nominal accounts to reporting headings (Figure 1).

We could allocate our categories to individual account codes by using the approximate form of the Excel VLOOKUP() or MATCH() functions or by merging a trial balance with our code chart Table using Power Query and the ability to Fill Down values.

The process was covered in the November/December 2016 issue of Chartech. Whichever method you use, the result should be something like this (Figure 2).

Dependent account list

If we were creating our accounts report and wanted to include specific accounts from the Cost of sales section, it would be useful to restrict our Data Validation source to the accounts in that section only. When working with Excel Tables and Data Validation, it’s a good idea to allocate a Range Name to the column that you want to use as the Data Validation source. This is because a Data Validation source based directly on the cells in an entire Excel Table column will only adjust automatically if the Data Validation cell and Excel Table are on the same worksheet.

Using a Range Name based on the entire Table column allows the Data Validation source to adjust whichever worksheet it is on. We could allocate the Range Name: Category to the Category column of our accounts chart Table and then use this Range Name as the List source for our top-level Data Validation cell. Once we have chosen a value from this list, we want our dependent Data Validation cells to only show nominal accounts in this section of our coding chart.

Having chosen our category, we can find the first cell in the Category column of our full list of account codes and values that matches the Category using the MATCH() function. We can also find out how many cells in the Category column match the category using COUNTIF() or COUNTIFS(). We have allocated the Range Name: FullCategory to the Category column of our full list of account codes and values.

In the example below, we have included our Table and ‘report’ on the same sheet so that we can see how it all works. In practice, we would keep the Table containing the data and the report cells on separate sheets. The Data Validation source of cell F6 is set to the Category Range Name from our accounts chart Table. We can use the value selected in this cell to find the first matching item in our FullCategory list and also the number of matching items. For the approach to work, our Table must be sorted by Category so that all the individual accounts in a category are together.

The Data Validation List source for the Code column of our accounts Table is entered as: =OFFSET(FullCategory,$F$7-1,1,$F$8,1)

We are using the ‘area’ version of OFFSET() to return a range rather than a single cell. Setting our first argument as a range means that OFFSET() will use the top left cell of that range as its starting point. We then use arguments two and three to move us to the first Cost of sales entry and one column to the right respectively. Arguments three and four define an area as many rows high as the number of Cost of sales matches and one column wide (Figure 3).

Although we have shown the various values in separate cells for clarity, we could combine them into a single Data Validation source formula: =OFFSET(FullCategory,MATCH($F$6,FullCategory,0) 1,1,COUNTIFS(FullCategory,$F$6),1)

For more information on the OFFSET() function, including some of the drawbacks of using this particular function, it’s worth searching the Excel Community.