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.