[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Dynamic Data Vaildation

Published on
4,771 Points
Last Modified:
Rob Henson
30 years in Financial Services and Management Accounting. CIMA trained but prefer the technical or system aspects of accounting.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Firstly take a look at the attached file.

Dynamic Data Validation.xlsx

This file shows 3 inter-related Data Validation lists. A selection from list 1 will restrict the list for selection 2 which will then restrict the list for selection 3.

In columns A to F I have created the list of values in true datagrid form. By true datagrid form I mean that each row/entry is unique but parts of that row will be repeated on other rows. For example, there are a number of entries in the Business and Professional Services list, ranging from HR to Finance to IT. If we take Finance as an example there are then numerous entries for Finance; Audit, Credit Cards, Payment Services etc. The entries for the final classification ie selection 3 will be unique but the values of Finance (level 2) and Business Services (level 1) will be repeated;

Category                  Business Area     Service

Business Services    Finance                 Audit

Business Services    Finance                 Credit Cards

Business Services    Finance                 Payment Services

Each option also has an alphanumeric identifier. Level 1 entries have IDs from SA001 to SA004, level 2 have codes SB001 to SB051 and level 3 have IDs from SC001 to SC242. All Business Service entries will have an SA code of SA001, all Finance entries then have a code of SB002, the codes at level 3 will then be unique per entry but for example the first of the Finance entries is Audit and is SC003.

The identifiers are calculated in columns H to M based on the value of columns B, D & F changing. The formula to create the numerical part of the code deliberately does not use static cell references so that when rows are inserted the formulae still refer to the row above rather than the original row above prior to inserting.

Columns S to U then create a list of identifiers created without the repetition and these are then used as a lookup value to create the list of descriptions in columns O to Q; thus creating three lists of entries which could be used as Data Validation (DV).

However, if these three lists are used for DV as independent lists they would not be dynamic and change based on the selection from the previous category because they are not related to each other in this form. A user could choose any value from each list.

Move over now to columns Y to AA. I am currently using this method to categorise a list of Suppliers to allocate 3 levels of categorisation based on the 3 lists created.

The three columns have Data Validation settings applied with the option set to choose from a list. Column Y simply refers to a Dynamic Named Range based on the non-blank values of column SA. The DV for columns Z and AA are created using an INDIRECT function and a range that is created in columns AM and AN. These ranges are created based on numbers in columns AF/AG and AI/AJ.

The numbers represent the rows from the 2 lists where the relevant values for the previous selection are held.

When the first option is selected in column Y the identifier for that choice is populated in column AB. This identifier is then used to find the identifiers for the first and last entries for that category in column D. These identifiers are then used to find the rows of those identifiers in columns S to U. These row numbers are then used in a concatenation with a sheet name and column reference to create a string of text recognisable as a range. This range text is then referred to in the INDIRECT function used in the DV settings for columns Z and AA.

The advantage of using the identifiers is that the values in the third category can be repeated, the conditional formatting on columns O to Q shows where there are duplicates in different category groups; eg HR Consultancy/Advice and Legal Consultancy/Advice.

If you wish to use this method, please do not hesitate to do so and if you have any queries please post them as a comment under this article. Posting as a comment would be better than sending a message as an answer to a query would then be visible to all viewers.

Author:Rob Henson
1 Comment
LVL 55

Expert Comment

by:Martin Liss
Thanks, that helps and I suggest you mention that the lists are related and also include the description of what you mean by "true database form". And rather than
Each option also then has an identifying code allocation. The codes are calculated in columns H to M...
I'd suggest something like
Each option also has an identifier which is generated via the formulas [that's more familiar to American audiences than formulae] found in columns H to M...
You should also change the reference to "code allocation" in you next to last paragraph.

I find
However, if these three lists are used for DV they would not be dynamic and change based on the selection from the previous category.
a little confusing. Do you mean
However, if these three were used for DV as is, they would not be dynamic because [explain why]

I'm going to publish this no matter what but consider my suggestions and let me know when you're done.

Featured Post

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Join & Write a Comment

Overview of OneDrive and collaboration.
Microsoft Office 365 Backup and Restore Solution by SysTools to export Office 365 mailbox to PST / EML file format on Windows OS. On Mac, tool backup O365 to PST / MBOX / MSG / EML / EMLX file formats. Not only this, restore option helps to import sā€¦
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month