How can I create dependent & dynamic DropDown lists that start with a certain string?

Hello,

Before I start describing the problem, please note that I have posted this same question on another forum, here is the link:
http://www.mrexcel.com/forum/excel-q...ml#post4634209


I would like to create 3 levels of dependent & dynamic DropDownlists (Data Validation lists) based on the following ranges:

DropDownList Levels-Ranges.png


The 3 ranges are created as: Level_1, Level_2, Level_3.

- DropDown list 1 must display items from range Level_1.
- DropDown list 2 must display FILTERED items from range Level_2. Example: if "1.ACTIVE" is selected on DropDownList 1 then the available items on DropDown 2 should only be: "1.1.ACTIVE CURRENT" and "1.2.ACTIVE NON CURRENT". The formula for the Data validation on DropDown 2 should filter the Level_2 range by the first 2 chars selected on DropDown1, in this case it should return a list of items that start with "1.", which are items 1.1. and 1.2.
- DropDown list 3 must display FILTERED items from range Level_3. Example: if "2.1. PASSIVE CURRENT" is selected on DropDownList 2 then the available items on DropDown 3 should only be: "2.1.1..." and "2.1.2. ...". The formula for the Data validation on DropDown 3 should filter the Level_3 range by the first 4 chars selected on DropDown2, in this case it should return a list of items that start with "2.1.", which are items 2.1.1. and 2.1.2.

I have tried with several OFFSET formulas and some worked, but unfortunately the OFFSET formula on the DropDowns stops working when you close and re-open the excel file. That's why I'm asking for your help, perhaps there is another way to achieve this?

Your help will be greatly appreciated.

Mc